<?php

namespace App\Kuafu\Statistics\Service;

use App\Kuafu\Base\Service\BaseService;
use App\Kuafu\Base\Util\DateTimeUtil;
use App\Kuafu\Common\Service\SeparateService;
use App\Kuafu\CouponExt\Enum\CouponExtEnum;
use App\Kuafu\CouponExt\Model\OrderCouponExtSplitModel;
use App\Kuafu\CouponPackage\Enum\CouponPackageEnum;
use App\Kuafu\CouponPackage\Model\CouponPackageOrderModel;
use App\Kuafu\CustomPayment\Enum\CustomPaymentEnum;
use App\Kuafu\CustomPayment\Model\CustomPaymentModel;
use App\Kuafu\Order\Model\Clickhouse\OrderCHModel;
use App\Kuafu\Order\Model\Clickhouse\OrderCouponCHModel;
use App\Kuafu\Order\Model\Clickhouse\OrderCouponExtCHModel;
use App\Kuafu\Order\Model\Clickhouse\OrderDetailCHModel;
use App\Kuafu\Order\Model\Clickhouse\OrderDiscountCHModel;
use App\Kuafu\Order\Model\Clickhouse\OrderExternalCHModel;
use App\Kuafu\Order\Model\OrderCouponSplitModel;
use App\Kuafu\Order\Model\OrderDetailSplitModel;
use App\Kuafu\Order\Model\OrderDiscountSplitModel;
use App\Kuafu\Order\Model\OrderExternalSplitModel;
use App\Kuafu\Order\Model\OrderSplitModel;
use App\Kuafu\Order\Service\OrderStatisticsService;
use App\Kuafu\Pay\Model\Clickhouse\PayStreamCHModel;
use App\Kuafu\Pay\Model\PayStreamModel;
use App\Kuafu\Pay\Model\RefundStreamModel;
use App\Kuafu\Pay\Model\ShopPaymentModel;
use App\Kuafu\Shop\Enum\ShopCodeEnum;
use App\Kuafu\Shop\Model\Shop;
use App\Kuafu\Shop\Service\ShopService;
use App\Kuafu\Statistics\Enum\CodeEnum;
use App\Kuafu\Order\Enum\CodeEnum as OrderEnum;
use App\Kuafu\Statistics\Enum\StatisticsCacheEnum;
use App\Kuafu\Statistics\Enum\StatisticsCodeEnum;
use App\Kuafu\Statistics\Exceptions\StatisticsException;
use App\Kuafu\WaiMai\Model\Clickhouse\WaiMaiOrderCHModel;
use App\Kuafu\WaiMai\Model\Clickhouse\WaiMaiOrderDetailCHModel;
use App\Kuafu\WaiMai\Model\WaiMaiOrderDetailModel;
use App\Kuafu\WaiMai\Model\WaiMaiOrderModel;
use App\Scope\ShopScope;
use Carbon\Carbon;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class StatisticsService extends BaseService
{
    protected static function paymentListBuilder(
        int $shopId = 0,
        int $payChannel = 0,
        string $startDate = '',
        string $endDate = '',
        string $payChannelMerchantId = '',
        string $engine = ''
    ) {
        if ($engine == SeparateService::DB) {
            $model = PayStreamModel::suffix($shopId, PayStreamModel::SYS_TYPE_RMS_MEAL)->withGlobalScope("shopScope", new ShopScope());
        } else {
            $model = PayStreamCHModel::query();
        }

        return $model
                             ->where('shopId', $shopId)
                             ->whereIn('sysType', [PayStreamModel::SYS_TYPE_RMS_MEAL, PayStreamModel::SYS_TYPE_RMS_COUPON_PACKAGE])
                             ->where('payStatus', PayStreamModel::PAY_STATUS_1)
                             ->where('isPaidIn', PayStreamModel::PAID_IN_YES)
                             ->with('refundStreamRel', 'shop')
                             ->when(strlen($payChannelMerchantId), function ($query) use ($payChannelMerchantId) {
                                 $shopIds = ShopPaymentModel::query()
                                                            ->where('payConfig', 'like',
                                                                "%\"mch_id\":\"{$payChannelMerchantId}\"%")
                                                            ->pluck('shopId')
                                                            ->toArray();
                                 if (count($shopIds)) {
                                     $query->whereRaw('`shopId` in (' . implode(',',
                                             $shopIds) . ') and payChannel = ' . PayStreamModel::PAY_CHANNEL_FY);
                                 } else {
                                     $query->whereRaw('shopId = -1');
                                 }
                             })
                             ->when($payChannel, function ($query) use ($payChannel) {
                                 $query->where('payChannel', $payChannel);
                             })
                             ->when(DateTimeUtil::isBaseDate($startDate), function ($query) use ($startDate) {
                                 $query->where("createdAt", '>=', strtotime($startDate));
                             })
                             ->when(DateTimeUtil::isBaseDate($endDate), function ($query) use ($endDate) {
                                 $query->where('createdAt', '<=', Carbon::parse($endDate)->endOfDay()->timestamp);
                             })
                             ->orderBy('createdAt', 'desc');
    }

    public static function paymentList(
        int $shopId,
        int $payChannel = 0,
        string $startDate,
        string $endDate,
        string $payChannelMerchantId = '',
        int $pageNum = 1,
        int $pageSize = 20
    ) {
        $engine = (new SeparateService(strtotime($startDate), strtotime($endDate)))->check();

        $query           = static::paymentListBuilder($shopId, $payChannel, $startDate, $endDate,
            $payChannelMerchantId, $engine);

        $totalCount = $query->count();

        $pay = (clone $query)
            ->when($engine == SeparateService::DB, function ($query) {
                $query->selectRaw("sum(payPrice) as payPrice, sum(fee) as fee");
            })
            ->when($engine == SeparateService::CLICKHOUSE, function ($query) {
                $query->select(raw("sum(payPrice) as payPrice"), raw("sum(fee) as fee"))->removeOrders();
            })
            ->get();

        $payPrice = (int)($pay->first()->payPrice ?? 0);
        $fee      = (int)($pay->first()->fee ?? 0);

        $settlementPrice = $payPrice - $fee;
        $models          = $query->simplePaginate($pageSize, ['*'], 'pageSize', $pageNum);
        $orderIds        = [];
        foreach ($models as $model) {
            $orderIds[$model->shopId][] = $model->orderId;
        }
        $orders = [];
        foreach ($orderIds as $shopId => $item) {

            if ($engine == SeparateService::DB) {
                $model = OrderSplitModel::suffix($shopId);
            } else {
                $model = OrderCHModel::query();
            }

            $orders[$shopId] = $model
                ->whereIn('orderId', $item)
                ->get();
        }
        $customPayList = CustomPaymentModel::query()->select(['id', 'name'])->get()->keyBy('id');

        $list  = $models->map(function ($payStream) use ($orders, $customPayList) {
            $orderSourceTypeText = '';
            foreach ($orders[$payStream->shopId] ?? [] as $order) {
                if ($order->orderId == $payStream->orderId) {
                    $orderSourceTypeText = $order->sourceTypeText;
                }
            }
            $payChannelName = $payStream->payChannelName ?: $customPayList->get($payStream->payChannel)->name ?? "";
            return [
                'shopId'                     => $payStream->shopId,
                'shopCode'                   => optional($payStream->shop)->shopCode,
                'shopName'                   => optional($payStream->shop)->name,
                'payChannelMerchantId'       => $payStream->payChannelMerchantId,
                'createdAt'                  => is_int($payStream->createdAt) ? date('Y-m-d H:i:s', $payStream->createdAt) : $payStream->createdAt->toDateTimeString(),
                'payPrice'                   => $payStream->payPrice,
                'fee'                        => $payStream->fee,
                'feeRate'                    => (100 * $payStream->feeRate) . '%',
                'settlementPrice'            => $payStream->settlementPrice,
                'payChannelName'             => $payChannelName,
                'payChannelSettlementPeriod' => $payStream->payChannelSettlementPeriod,
                'settlementType'             => '到余额',
                'payActionText'              => $payStream->payActionText,
                'orderSourceTypeText'        => $orderSourceTypeText,
                'thirdPayNo'                 => $payStream->thirdPayNo,
                'payNo'                      => $payStream->payNo,
                'orderId'                    => (string)$payStream->orderId,
                'refunds'                    => $payStream->refundStreamRel->map(function ($refundStream) {
                    return [
                        'refundNo'         => $refundStream->refundNo,
                        'refundPrice'      => $refundStream->refundPrice,
                        'refundStatus'     => $refundStream->refundStatus,
                        'refundStatusText' => $refundStream->refundStatusText,
                    ];
                })
            ];
        })->toArray();
        $count = $totalCount;
        $total = ['payPrice' => $payPrice, 'fee' => $fee, 'settlementPrice' => $settlementPrice];
        return compact('list', 'pageNum', 'pageSize', 'count', 'total');
    }

    public static function paymentExcel(
        int $shopId,
        int $payChannel = 0,
        string $startDate,
        string $endDate,
        string $payChannelMerchantId = ''
    ) {
        $total = [
            'payPrice'        => 0,
            'fee'             => 0,
            'settlementPrice' => 0,
            'refundPrice'     => 0,
        ];
        @ini_set('memory_limit', '512M');
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('A1', '支付结算明细');
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A1')->getFont()->setSize(16);
        $sheet->mergeCells("A1:S1");

        $headers = [
            '门店编号',
            '门店',
            '富友商户号',
            '交易时间',
            '交易金额',
            '手续费',
            '费率',
            '结算金额',
            '支付方式',
            '结算周期',
            '结算方式',
            '结账方式',
            '业务类型',
            '第三方交易单号',
            '支付流水号',
            '业务单号',
            '退款单号',
            '退款状态'
        ];
        $column  = 1;
        foreach ($headers as $header) {
            $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '2', $header,
                DataType::TYPE_STRING);
            $column++;
        }
        $line = 3;

        $engine = (new SeparateService(strtotime($startDate), strtotime($endDate)))->check();

        static::paymentListBuilder($shopId, $payChannel, $startDate, $endDate, $payChannelMerchantId, $engine)
              ->chunk(100, function ($models) use (&$total, &$line, $sheet, $engine) {
                  $orderIds = [];
                  foreach ($models as $model) {
                      $orderIds[$model->shopId][] = $model->orderId;
                  }
                  $orders = [];
                  foreach ($orderIds as $shopId => $item) {

                      if ($engine == SeparateService::DB) {
                          $model = OrderSplitModel::suffix($shopId);
                      } else {
                          $model = OrderCHModel::query();
                      }

                      $orders[$shopId] = $model
                          ->whereIn('orderId', $item)
                          ->get();
                  }
                  foreach ($models as $payStream) {
                      $refundStreamCount   = count($payStream->refundStreamRel);
                      $orderSourceTypeText = '';
                      foreach ($orders[$payStream->shopId] ?? [] as $order) {
                          if ($order->orderId == $payStream->orderId) {
                              $orderSourceTypeText = $order->sourceTypeText;
                          }
                      }
                      $sheet->setCellValueExplicit("A{$line}", (string)optional($payStream->shop)->shopCode,
                          DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("B{$line}", (string)optional($payStream->shop)->name,
                          DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("C{$line}", (string)$payStream->payChannelMerchantId,
                          DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("D{$line}", is_int($payStream->createdAt) ? date('Y-m-d H:i:s', $payStream->createdAt) : (string)$payStream->createdAt->toDateTimeString(), DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("E{$line}", $payStream->payPrice / 100, DataType::TYPE_NUMERIC);
                      $total['payPrice'] += $payStream->payPrice;
                      $sheet->setCellValueExplicit("F{$line}", $payStream->fee / 100, DataType::TYPE_NUMERIC);
                      $total['fee'] += $payStream->fee;
                      $sheet->setCellValueExplicit("G{$line}", $payStream->feeRate, DataType::TYPE_NUMERIC);
                      $sheet->getStyle("G{$line}")->getNumberFormat()
                            ->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);
                      $sheet->setCellValueExplicit("H{$line}", $payStream->settlementPrice / 100,
                          DataType::TYPE_NUMERIC);
                      $total['settlementPrice'] += $payStream->settlementPrice;
                      $sheet->setCellValueExplicit("I{$line}", $payStream->payChannelName, DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("J{$line}", $payStream->payChannelSettlementPeriod,
                          DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("K{$line}", '到余额', DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("L{$line}", $payStream->payActionText, DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("M{$line}", $orderSourceTypeText, DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("N{$line}", $payStream->thirdPayNo, DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("O{$line}", $payStream->payNo, DataType::TYPE_STRING);
                      $sheet->setCellValueExplicit("P{$line}", $payStream->orderId, DataType::TYPE_STRING);
                      // 判断退款情况
                      $line++;
                      if (!$refundStreamCount) {
                          continue;
                      }
                      foreach ($payStream->refundStreamRel as $refundStream) {
                          if (in_array($refundStream->refundStatus,
                              [RefundStreamModel::REFUND_STATUS_ING, RefundStreamModel::REFUND_STATUS_OK])) {
                              // 退款正常的
                              $sheet->setCellValueExplicit("E{$line}", 0 - ($refundStream->refundPrice / 100),
                                  DataType::TYPE_NUMERIC);
                              $sheet->setCellValueExplicit("F{$line}", 0 - ($refundStream->refundFee / 100),
                                  DataType::TYPE_NUMERIC);
                              $sheet->setCellValueExplicit("H{$line}", 0 - ($refundStream->settlementPrice / 100),
                                  DataType::TYPE_NUMERIC);
                          } else {
                              // 退款不正常
                              $sheet->setCellValueExplicit("E{$line}", 0, DataType::TYPE_NUMERIC);
                              $sheet->setCellValueExplicit("F{$line}", 0, DataType::TYPE_NUMERIC);
                              $sheet->setCellValueExplicit("H{$line}", 0, DataType::TYPE_NUMERIC);
                          }
                          $sheet->setCellValueExplicit("A{$line}", (string)optional($payStream->shop)->shopCode,
                              DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("B{$line}", (string)optional($payStream->shop)->name,
                              DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("C{$line}", (string)$payStream->payChannelMerchantId,
                              DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("D{$line}", (string)$refundStream->createdAt->toDateTimeString(),
                              DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("G{$line}", $payStream->feeRate, DataType::TYPE_NUMERIC);
                          $sheet->getStyle("G{$line}")->getNumberFormat()
                                ->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);
                          $sheet->setCellValueExplicit("I{$line}", $payStream->payChannelName, DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("J{$line}", $payStream->payChannelSettlementPeriod,
                              DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("K{$line}", '退款', DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("L{$line}", '退款', DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("M{$line}", $orderSourceTypeText, DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("N{$line}", $payStream->thirdPayNo, DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("O{$line}", $payStream->payNo, DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("P{$line}", $payStream->orderId, DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("Q{$line}", $refundStream->refundNo, DataType::TYPE_STRING);
                          $sheet->setCellValueExplicit("S{$line}", $refundStream->refundStatusText,
                              DataType::TYPE_STRING);
                          $line++;
                      }
                  }
              });
        // 最后的统计
        $sheet->setCellValue("E" . ($line + 1), "=SUM(E3:E" . ($line - 1) . ")");
        $sheet->setCellValue("F" . ($line + 1), "=SUM(F3:F" . ($line - 1) . ")");
        $sheet->setCellValue("H" . ($line + 1), "=SUM(H3:H" . ($line - 1) . ")");

        $shopName = '';
        if ($shopId) {
            $shop     = Shop::find($shopId);
            $shopName = (string)optional($shop)->name;
            if (strlen($shopName)) {
                $shopName = "【{$shopName}】";
            }
        }
        $tmpFile = sys_get_temp_dir() . "/{$shopName}财务对账" . date('YmdHis') . '.xlsx';
        IOFactory::createWriter($spreadsheet, 'Xlsx')->save($tmpFile);
        return $tmpFile;
    }

    /*
     * 综合营业统计
     */
    public static function comprehensiveList($shopId, $monthDate, $today)
    {
        if (config('app.env') == 'qa' || config('app.env') == 'pre') {
            $time = BaseService::getMonthTime($monthDate, 1);
        } else {
            $time = BaseService::getMonthTime($monthDate, $today);
        }
        if (!count($time['time']) || !$time['count'] || $shopId == 0) {
            return [
                'outCouponIncome'   => [],
                'outCouponDiscount' => [],
                'inCoupon'          => [],
                'lists'             => [],
                'total'             => (object)[]
            ];
        }
        $redisKey = StatisticsCacheEnum::getRedisKey($shopId, $time['time'][0] . $time['time'][1]);
        $tag      = StatisticsCacheEnum::STATISTICAL_TOTAL_CACHE_TAG;

        $rdData = Cache::store('redis')->tags($tag)->get($redisKey);
        if ($rdData) {
            return $rdData;
        }

        // 结束时间，距今10天内的订单需要使用final
        // 用final会把服务器直接跑死
        $final = false;
//        if (time() - $time['time'][1] < 86400 * 10) {
//            $final = true;
//        }

        $income = [];
        //第三方核销优惠券名称（外部）
        //美团
        $outCouponIncome   = [];
        $outCouponDiscount = [];
        //抖音
        $dyCouponIncome    = [];
        $dyCouponDiscount  = [];
        //小红书
        $xhsCouponIncome    = [];
        $xhsCouponDiscount  = [];

        $couponTitle = OrderCouponExtCHModel::query()
                                            ->when($final, function ($query) {
                $query->final();
            })
                                            ->where('shopId', $shopId)
                                            ->where('extPlatform', CouponExtEnum::EXT_PLATFORM_MEITUAN)
                                            ->where('couponExtStatus', CodeEnum::ORDER_COUPON_STATUS_USE)
                                            ->whereBetween('createdAt', $time['time'])
                                            ->select(raw('distinct(`couponExtName`) as couponExtName'))
                                            ->get()
                                            ->toArray();
        if (count($couponTitle)) {
            foreach ($couponTitle as $k => $coupon) {
                //美团
                $outCouponIncome[]   = [
                    'name'   => $coupon['couponExtName'],
                    'key'    => 'couponOutIncome' . $k,
                    'amount' => 0
                ];
                $outCouponDiscount[] = [
                    'name'   => $coupon['couponExtName'],
                    'key'    => 'couponOutDiscount' . $k,
                    'amount' => 0
                ];
            }
        }

        $couponTitle1 = OrderCouponExtCHModel::query()
                                             ->when($final, function ($query) {
                $query->final();
            })
                                             ->where('shopId', $shopId)
                                             ->where('extPlatform', CouponExtEnum::EXT_PLATFORM_DOUYIN)
                                             ->where('couponExtStatus', CodeEnum::ORDER_COUPON_STATUS_USE)
                                             ->whereBetween('createdAt', $time['time'])
                                             ->select(raw('distinct(`couponExtName`) as couponExtName'))
                                             ->get()
                                             ->toArray();
        if (count($couponTitle1)) {
            foreach ($couponTitle1 as $k => $coupon) {
                //抖音
                $dyCouponIncome[]   = [
                    'name'   => $coupon['couponExtName'],
                    'key'    => 'dyCouponIncome' . $k,
                    'amount' => 0
                ];
                $dyCouponDiscount[] = [
                    'name'   => $coupon['couponExtName'],
                    'key'    => 'dyCouponDiscount' . $k,
                    'amount' => 0
                ];
            }
        }

            $xhsCouponTitle = OrderCouponExtCHModel::query()
                                                   ->when($final, function ($query) {
                    $query->final();
                })
                                                   ->where('shopId', $shopId)
                                                   ->where('extPlatform', CouponExtEnum::EXT_PLATFORM_XHS)
                                                   ->where('couponExtStatus', CodeEnum::ORDER_COUPON_STATUS_USE)
                                                   ->whereBetween('createdAt', $time['time'])
                                                   ->select(raw('distinct(`couponExtName`) as couponExtName'))
                                                   ->get()
                                                   ->toArray();

        if (count($xhsCouponTitle)) {
            foreach ($xhsCouponTitle as $k => $coupon) {
                //小红书
                $xhsCouponIncome[]   = [
                    'name'   => $coupon['couponExtName'],
                    'key'    => 'xhsCouponIncome' . $k,
                    'amount' => 0
                ];
                $xhsCouponDiscount[] = [
                    'name'   => $coupon['couponExtName'],
                    'key'    => 'xhsCouponDiscount' . $k,
                    'amount' => 0
                ];
            }
        }
        //获得所有内部优惠券名称（内部）
        $insideCouponName   = [];   //内部优惠券支付优惠构成
        $insideCouponIncome = [];   //内部优惠券营业收入
        $insideCoupon = OrderCouponCHModel::query()
            ->when($final, function ($query) {
                $query->final();
            })
            ->where('status', CodeEnum::ORDER_COUPON_STATUS_USE)
            ->whereBetween('createdAt', $time['time'])
            ->select(raw('distinct(`couponName`) as couponName'))
            ->get()
            ->toArray();
        if (count($insideCoupon)) {
            foreach ($insideCoupon as $k => $coupon) {
                $insideCouponName[]   = [
                    'name'   => $coupon['couponName'],
                    'key'    => 'couponIn' . $k,
                    'amount' => 0
                ];
                $insideCouponIncome[] = [
                    'name'   => $coupon['couponName'],
                    'key'    => 'couponIncome' . $k,
                    'amount' => 0
                ];
            }
        }

        //自定义支付
        $customPayment            = [];  //自定义支付
        $customPaymentDiscount    = [];  //自定义支付优惠
        $customPaymentKey         = 0;
        $customPaymentDiscountKey = 0;
        $customPaymentPayChannels = PayStreamCHModel::query()
            ->when($final, function ($query) {
                $query->final();
            })
            ->where('shopId', $shopId)
            ->where('payStatus', CodeEnum::PAY_STATUS_NORMAL)
            ->where('payChannel', '>', 100)
            ->select(raw('distinct(`payChannel`) as payChannel'))
            ->whereBetween('createdAt', $time['time'])
            ->select('payChannel')->get()->pluck('payChannel');

        CustomPaymentModel::query()
            ->where('status', CustomPaymentEnum::STATUS_ON)
            ->whereIn('id', $customPaymentPayChannels)
            ->get()
            ->each(function ($item) use (
                &$customPayment,
                &$customPaymentDiscount,
                &$customPaymentKey,
                &$customPaymentDiscountKey
            ) {
                switch ($item->reckonType) {
                    case CustomPaymentEnum::RECKON_TYPE_INCOME:
                        $customPayment[] = [
                            'name'   => $item->name,
                            'key'    => 'customPayment' . $customPaymentKey,
                            'amount' => 0,
                            'id'     => $item->id,
                            'refund' => 0,
                        ];
                        $customPaymentKey++;
                        break;
                    case CustomPaymentEnum::RECKON_TYPE_DISCOUNT:
                        $customPaymentDiscount[] = [
                            'name'   => $item->name,
                            'key'    => 'customPaymentDiscount' . $customPaymentDiscountKey,
                            'amount' => 0,
                            'id'     => $item->id,
                            'refund' => 0,
                        ];
                        $customPaymentDiscountKey++;
                        break;
                }
            });
        for ($i = 1; $i <= $time['count']; $i++) {
            if (strlen($i) == 1) {
                $income[date('Y-m', $time['time'][0]) . '-0' . $i] = [
                    'date'                       => date('Y-m', $time['time'][0]) . '-0' . $i,
                    'hotelTurnover'              => 0,
                    'hotelIncome'                => 0,
                    'hotelDiscountPrice'         => 0,
                    'hotelNum'                   => 0,
                    'hotelFrontPrice'            => 0,
                    'hotelAfterPrice'            => 0,
                    'mtTurnover'                 => 0,
                    'mtIncome'                   => 0,
                    'mtDiscountPrice'            => 0,
                    'mtNum'                      => 0,
                    'mtFrontPrice'               => 0,
                    'mtAfterPrice'               => 0,
                    'elmTurnover'                => 0,
                    'elmIncome'                  => 0,
                    'elmDiscountPrice'           => 0,
                    'elmNum'                     => 0,
                    'couponPackageTurnover'      => 0,
                    'couponPackageIncome'        => 0,
                    'couponPackageDiscountPrice' => 0,
                    'couponPackageNum'           => 0,
                    'elmFrontPrice'              => 0,
                    'elmAfterPrice'              => 0,
                    'boxesTurnover'              => 0,
                    'boxesIncome'                => 0,
                    'boxesDiscountPrice'         => 0,
                    'cashOrigin'                 => 0,
                    'fuyouOrigin'                => 0,
                    'elm'                        => 0,
                    'mt'                         => 0,
                    'incomeGroupBuy'             => $outCouponIncome,
                    'discountGroupBuy'           => $outCouponDiscount,
                    'dyCouponIncome'             => $dyCouponIncome,
                    'dyCouponDiscount'           => $dyCouponDiscount,
                    'xhsCouponIncome'             => $xhsCouponIncome,
                    'xhsCouponDiscount'           => $xhsCouponDiscount,
                    'internalCoupon'             => $insideCouponName,
                    'insideCouponIncome'         => $insideCouponIncome,
                    'giveFood'                   => 0,
                    'discount'                   => 0,
                    'reduction'                  => 0,
                    'orderDishesNum'             => 0,
                    'waimaiDishesNum'            => 0,
                    'mtActivityExpenditure'      => 0,
                    'mtPlatformPrice'            => 0,
                    'elmActivityExpenditure'     => 0,
                    'elmPlatformPrice'           => 0,
                    'mtPackingPrice'             => 0,
                    'mtShippingPrice'            => 0,
                    'elmPackingPrice'            => 0,
                    'elmShippingPrice'           => 0,
                    'mtOtherExpenses'            => 0,
                    'elmOtherExpenses'           => 0,
                    'customPayment'              => $customPayment,
                    'customPaymentDiscount'      => $customPaymentDiscount,
                    'dyTurnover'                => 0,
                    'dyIncome'                  => 0,
                    'dyDiscountPrice'           => 0,
                    'dyNum'                     => 0,
                    'dyFrontPrice'              => 0,
                    'dyAfterPrice'              => 0,
                    'dy'                        => 0,
                    'dyActivityExpenditure'     => 0,
                    'dyPlatformPrice'           => 0,
                    'dyPackingPrice'            => 0,
                    'dyShippingPrice'           => 0,
                    'dyOtherExpenses'           => 0,
                ];
            } else {
                $income[date('Y-m', $time['time'][0]) . '-' . $i] = [
                    'date'                       => date('Y-m', $time['time'][0]) . '-' . $i,
                    'hotelTurnover'              => 0,
                    'hotelIncome'                => 0,
                    'hotelDiscountPrice'         => 0,
                    'hotelNum'                   => 0,
                    'hotelFrontPrice'            => 0,
                    'hotelAfterPrice'            => 0,
                    'mtTurnover'                 => 0,
                    'mtIncome'                   => 0,
                    'mtDiscountPrice'            => 0,
                    'mtNum'                      => 0,
                    'mtFrontPrice'               => 0,
                    'mtAfterPrice'               => 0,
                    'elmTurnover'                => 0,
                    'elmIncome'                  => 0,
                    'elmDiscountPrice'           => 0,
                    'elmNum'                     => 0,
                    'couponPackageTurnover'      => 0,
                    'couponPackageIncome'        => 0,
                    'couponPackageDiscountPrice' => 0,
                    'couponPackageNum'           => 0,
                    'elmFrontPrice'              => 0,
                    'elmAfterPrice'              => 0,
                    'boxesTurnover'              => 0,
                    'boxesIncome'                => 0,
                    'boxesDiscountPrice'         => 0,
                    'cashOrigin'                 => 0,
                    'fuyouOrigin'                => 0,
                    'elm'                        => 0,
                    'mt'                         => 0,
                    'incomeGroupBuy'             => $outCouponIncome,
                    'discountGroupBuy'           => $outCouponDiscount,
                    'dyCouponIncome'             => $dyCouponIncome,
                    'dyCouponDiscount'           => $dyCouponDiscount,
                    'xhsCouponIncome'             => $xhsCouponIncome,
                    'xhsCouponDiscount'           => $xhsCouponDiscount,
                    'internalCoupon'             => $insideCouponName,
                    'insideCouponIncome'         => $insideCouponIncome,
                    'giveFood'                   => 0,
                    'discount'                   => 0,
                    'reduction'                  => 0,
                    'orderDishesNum'             => 0,
                    'waimaiDishesNum'            => 0,
                    'mtActivityExpenditure'      => 0,
                    'mtPlatformPrice'            => 0,
                    'elmActivityExpenditure'     => 0,
                    'elmPlatformPrice'           => 0,
                    'mtPackingPrice'             => 0,
                    'mtShippingPrice'            => 0,
                    'elmPackingPrice'            => 0,
                    'elmShippingPrice'           => 0,
                    'mtOtherExpenses'            => 0,
                    'elmOtherExpenses'           => 0,
                    'customPayment'              => $customPayment,
                    'customPaymentDiscount'      => $customPaymentDiscount,
                    'dyTurnover'                => 0,
                    'dyIncome'                  => 0,
                    'dyDiscountPrice'           => 0,
                    'dyNum'                     => 0,
                    'dyFrontPrice'              => 0,
                    'dyAfterPrice'              => 0,
                    'dy'                        => 0,
                    'dyActivityExpenditure'     => 0,
                    'dyPlatformPrice'           => 0,
                    'dyPackingPrice'            => 0,
                    'dyShippingPrice'           => 0,
                    'dyOtherExpenses'           => 0,
                ];
            }
        }
        $didNotPay = [];  //正常的订单
        $normalOrder = [];  //正常的订单（不区分时间）
//        OrderSplitModel::suffix($shopId)

        $start = microtime(true);

        $page = 0;
        while (true) {
            $query = OrderCHModel::query()
                                    ->when($final, function ($query) {
                                        $query->final();
                                    })
                                 ->whereBetween('completeTime', $time['time'])
                                 ->where('shopId', $shopId)
                                 ->where('orderStatus', OrderEnum::ORDER_STATUS_FINISHED)
                                 ->where('refundStatus', '!=', \App\Kuafu\Order\Enum\CodeEnum::ORDER_REFUND_STATUS_ALL)
                                 ->select([
                                     'orderId',
                                     'originalPrice',
                                     'realPayPrice',
                                     'realDiscountPrice',
                                     'completeTime',
                                     'shopId'
                                 ])
                                ->orderBy('orderId')

                //                                 ->with([
//                                     'orderCouponRel' => function ($query) {
//                                         $query->whereIn('status',
//                                             [
//                                                 OrderCouponSplitModel::STATUS_USED,
//                                                 OrderCouponSplitModel::STATUS_WAIT
//                                             ]);
//                                     }
//                                 ])
                                 ->limit(1000, $page * 1000)
                                 ->get();

            $orderIdTimes = $query->pluck('completeTime', 'orderId')->toArray();

            OrderCouponCHModel
                ::query()
                ->when($final, function ($q) {
                    $q->final();
                })
                ->whereIn('orderId', $query->pluck('orderId')->toArray())
                ->whereIn('status',
                    [
                        OrderCouponSplitModel::STATUS_USED,
                        OrderCouponSplitModel::STATUS_WAIT
                    ])
                ->orderBy('id')
                ->get()
                ->each(function ($orderCoupon) use (
                    &$income,
                    &$insideCouponName,
                    &$insideCouponIncome,
                    $orderIdTimes
                ) {

                    $completeTime = $orderIdTimes[$orderCoupon->orderId] ?? '';
                    if (!$completeTime) {
                        return;
                    }

                    if ($completeTime instanceof Carbon) {
                        $date = $completeTime->toDateString();
                    } else {
                        $date = Carbon::createFromTimestamp($completeTime)
                                      ->toDateString();
                    }

                    $couponName = $orderCoupon->couponName;
                    foreach ($insideCouponName as $k => $v) {
                        if ($v['name'] == $couponName) {
                            $income[$date]['internalCoupon'][$k]['amount'] += (int)$orderCoupon->couponDiscountPrice - (int)$orderCoupon->couponCostPrice;
                        }
                    }
                    foreach ($insideCouponIncome as $k => $v) {
                        if ($v['name'] == $couponName) {
                            $income[$date]['insideCouponIncome'][$k]['amount'] += (int)$orderCoupon->couponCostPrice;
                        }
                    }

                });

                 $query->each(function ($order) use (
                     &$income,
                     &$didNotPay,
                     &$normalOrder,
                     &$insideCouponName,
                     &$insideCouponIncome
                 ) {
                     if ($order->completeTime instanceof Carbon) {
                         $date = $order->completeTime->toDateString();
                     } else {
                         $date = Carbon::createFromTimestamp($order->completeTime)
                                       ->toDateString();
                     }
                     if (!isset($income[$date])) {
                         return;
                     }
                     $income[$date]['date']               = $date;
                     $income[$date]['hotelTurnover']      += (int)$order->originalPrice;
                     $income[$date]['hotelIncome']        += (int)$order->realPayPrice;
                     $income[$date]['hotelDiscountPrice'] += (int)$order->realDiscountPrice;
                     $income[$date]['hotelNum']           += 1;
                     $didNotPay[$date][]                  = $order->orderId;
                     $normalOrder[]                       = $order->orderId;
                 });

            if ($query->count() == 0) {
                break;
            }

            $page++;
        }

        Log::info("OrderCHModel & OrderCouponCHModel 耗时：" . (microtime(true) - $start) . "秒");
        $start = microtime(true);

        CouponPackageOrderModel::query()
            ->whereBetween('createdAt', $time['time'])
            ->where('shopId', $shopId)
            ->where('payStatus', CouponPackageEnum::PAY_STATUS_ON)
            ->chunk(1000, function ($orders) use (&$income) {
                foreach ($orders as $order) {
                    if ($order->createdAt instanceof Carbon) {
                        $date = $order->createdAt->toDateString();
                    } else {
                        $date = Carbon::createFromTimestamp($order->createdAt)
                            ->toDateString();
                    }
                    if (!isset($income[$date])) {
                        continue;
                    }
                    $income[$date]['couponPackageTurnover']      += (int)$order->payPrice;
                    $income[$date]['couponPackageIncome']        += (int)$order->payPrice;
                    $income[$date]['couponPackageDiscountPrice'] += 0;
                    $income[$date]['couponPackageNum']           += 1;
                }
            });

        Log::info("CouponPackageOrderModel 耗时：" . (microtime(true) - $start) . "秒");
        $start = microtime(true);

        $waimaiOrderId = [];

        $page = 0;
        while(true) {
            $query = WaiMaiOrderCHModel::query()
                                ->when($final, function ($query) {
                                    $query->final();
                                })
                              ->whereBetween('createdAt', $time['time'])
                              ->where('shopId', $shopId)
                              ->where('orderStatus', '!=', \App\Kuafu\WaiMai\Enum\WaiMaiDBEnum::ORDER_STATUS_7)
                              ->select([
                                  'shopId',
                                  'createdAt',
                                  'sourceType',
                                  'originalPrice',
                                  'originalShippingPrice',
                                  'packingPrice',
                                  'expectIncome',
                                  'discountPrice',
                                  'orderId',
                                  'platformPrice',
                                  'activityExpenditure',
                                  'shippingPrice',
                                  'otherPrice'
                              ])
                              ->orderBy('orderId')
                ->limit(1000, $page * 1000)
                ->get()
                ->each(function ($order) use (&$income, &$waimaiOrderId) {
                    if ($order->createdAt instanceof Carbon) {
                        $date = $order->createdAt->toDateString();
                    } else {
                        $date = Carbon::createFromTimestamp($order->createdAt)
                                      ->toDateString();
                    }

                    if (!isset($income[$date])) {
                        return;
                    }
                    if ($order->sourceType == CodeEnum::ORIDER_SOURCE_MT) {
                        $income[$date]['mtTurnover']            += ((int)$order->originalPrice - (int)$order->originalShippingPrice - (int)$order->packingPrice);
                        $income[$date]['mtIncome']              += (int)$order->expectIncome;
                        $income[$date]['mtDiscountPrice']       += (int)$order->discountPrice;
                        $income[$date]['mtNum']                 += 1;
                        $income[$date]['mt']                    += (int)$order->expectIncome;
                        $income[$date]['mtPlatformPrice']       += (int)$order->platformPrice;
                        $income[$date]['mtActivityExpenditure'] += (int)$order->activityExpenditure;
                        $income[$date]['mtPackingPrice']        += (int)$order->packingPrice;
                        $income[$date]['mtShippingPrice']       += (int)$order->shippingPrice;
                        $income[$date]['mtOtherExpenses']       += (int)$order->otherPrice;
                    }
                    if ($order->sourceType == CodeEnum::ORIDER_SOURCE_ELM) {
                        $income[$date]['elmTurnover']            += ((int)$order->originalPrice - (int)$order->originalShippingPrice - (int)$order->packingPrice);
                        $income[$date]['elmIncome']              += (int)$order->expectIncome;
                        $income[$date]['elmDiscountPrice']       += (int)$order->discountPrice;
                        $income[$date]['elmNum']                 += 1;
                        $income[$date]['elm']                    += (int)$order->expectIncome;
                        $income[$date]['elmPlatformPrice']       += (int)$order->platformPrice;
                        $income[$date]['elmActivityExpenditure'] += (int)$order->activityExpenditure;
                        $income[$date]['elmPackingPrice']        += (int)$order->packingPrice;
                        $income[$date]['elmShippingPrice']       += (int)$order->shippingPrice;
                        $income[$date]['elmOtherExpenses']       += (int)$order->otherPrice;
                    }
                    if ($order->sourceType == CodeEnum::ORIDER_SOURCE_DY) {
                        $income[$date]['dyTurnover']            += ((int)$order->originalPrice - (int)$order->originalShippingPrice - (int)$order->packingPrice);
                        $income[$date]['dyIncome']              += (int)$order->expectIncome;
                        $income[$date]['dyDiscountPrice']       += (int)$order->discountPrice;
                        $income[$date]['dyNum']                 += 1;
                        $income[$date]['dy']                    += (int)$order->expectIncome;
                        $income[$date]['dyPlatformPrice']       += (int)$order->platformPrice;
                        $income[$date]['dyActivityExpenditure'] += (int)$order->activityExpenditure;
                        $income[$date]['dyPackingPrice']        += (int)$order->packingPrice;
                        $income[$date]['dyShippingPrice']       += (int)$order->shippingPrice;
                        $income[$date]['dyOtherExpenses']       += (int)$order->otherPrice;
                    }
                    $waimaiOrderId[$date][] = $order->orderId;
                });

            if ($query->count() == 0) {
                break;
            }

            $page++;
        }

        Log::info("WaiMaiOrderCHModel 耗时：" . (microtime(true) - $start) . "秒");
        $start = microtime(true);

        $id = 0;
        while (true) {
            $query = OrderDetailCHModel::query()
                                ->when($final, function ($query) {
                                    $query->final();
                                })
                              ->where('shopId', $shopId)
                              ->where('createdAt', '>=', $time['time'][0])
                              ->where('createdAt', '<', $time['time'][1])
//                              ->select([
//                                  'shopId',
//                                  'createdAt',
//                                  'type',
//                                  'num',
//                                  'refundNum',
//                                  'discountType',
//                                  'discountPrice',
//                                  'orderId',
//                                  'freeNum',
//                                  'originalPrice',
//                                  'packageDetailId'
//                              ])
                    ->where('id', '>', $id)
                ->orderBy('id')
                ->limit(1000)
                ->get()
                ->each(function ($order)  use (&$income, &$didNotPay) {
                    if ($order->createdAt instanceof Carbon) {
                        $date = $order->createdAt->toDateString();
                    } else {
                        $date = Carbon::createFromTimestamp($order->createdAt)
                                      ->toDateString();
                    }
                    if (!isset($didNotPay[$date])) {
                        $didNotPay[$date] = [];
                    }
                    if ($order->type == 0) {
                        if (in_array($order->orderId, $didNotPay[$date])) {
                            $income[$date]['orderDishesNum'] += ((int)$order->num - (int)$order->refundNum);
                            if ($order->discountType == OrderEnum::ORDER_DISCOUNT_TYPE_COUNT) {
                                $income[$date]['discount'] += (int)$order->discountPrice;
                            }
                            if ($order->discountType == OrderEnum::ORDER_DISCOUNT_TYPE_ANNUL) {
                                $income[$date]['reduction'] += (int)$order->discountPrice;
                            }
                        }
                    }
                    if ($order->type == 0 && $order->packageDetailId == 0) {
                        if (in_array($order->orderId, $didNotPay[$date])) {
                            if ($order->freeNum > 0) {
                                $income[$date]['giveFood'] += (int)$order->originalPrice;
                            }
                        }
                    }
                    if ($order->type == 1 && $order->freeNum > 0) {
                        if (in_array($order->orderId, $didNotPay[$date])) {
                            $income[$date]['giveFood'] += (int)$order->originalPrice;
                        }
                    }
                });

            if ($query->count() == 0) {
                break;
            }

            // 找到最后一条id
            $id = $query->last()->id;

            Log::info("OrderDetailCHModel {$id} 耗时：" . (microtime(true) - $start) . "秒");
            $start = microtime(true);
        }

        Log::info("OrderDetailCHModel 耗时：" . (microtime(true) - $start) . "秒");
        $start = microtime(true);

//        OrderDetailSplitModel::suffix($shopId)

        collect($normalOrder)->chunk(1000)->each(function ($orderIds) use (&$income, $didNotPay, $final, $shopId) {

            OrderDiscountCHModel::query()
                                ->when($final, function ($query) {
                                    $query->final();
                                })
                                ->where('shopId', $shopId)
                                ->whereIn('orderId', $orderIds->values()->toArray())
                                ->get()
                                ->each(function ($order) use (&$income, $didNotPay) {
                                        if ($order->createdAt instanceof Carbon) {
                                            $date = $order->createdAt->toDateString();
                                        } else {
                                            $date = Carbon::createFromTimestamp($order->createdAt)
                                                          ->toDateString();
                                        }
                                        if (!isset($income[$date])) {
                                            return;
                                        }

                                        if (in_array($order->orderId, $didNotPay[$date])) {
                                            if ($order->discountType == OrderEnum::PRODUCTION_DISCOUNT_TYPE_COUNT) {
                                                $income[$date]['discount'] += (int)$order->discountPrice;
                                            } elseif ($order->discountType == OrderEnum::PRODUCTION_DISCOUNT_TYPE_ANNUL) {
                                                $income[$date]['reduction'] += (int)$order->discountPrice;
                                            }
                                        }
                                });
        });

        Log::info("OrderDiscountSplitModel 耗时：" . (microtime(true) - $start) . "秒");
        $start = microtime(true);

        $id = 0;
        while (true) {

            $query =    WaiMaiOrderDetailCHModel::query()
                                                ->when($final, function ($query) {
                                                    $query->final();
                                                })
                                                ->where('shopId', $shopId)
                                                ->whereBetween('createdAt', $time['time'])
                                                ->select(['id', 'shopId', 'createdAt', 'orderId', 'num', 'productType', 'originalPrice', 'incomes'])
                ->where('id', '>', $id)
                ->limit(1000)
                ->orderBy('id')
                ->get()
                ->each(function ($order) use (&$income, &$waimaiOrderId) {
                    if ($order->createdAt instanceof Carbon) {
                        $date = $order->createdAt->toDateString();
                    } else {
                        $date = Carbon::createFromTimestamp($order->createdAt)
                                      ->toDateString();
                    }
                    if (!isset($waimaiOrderId[$date])) {
                        $waimaiOrderId[$date] = [];
                    }
                    if (in_array($order->orderId, $waimaiOrderId[$date])) {
                        $income[$date]['waimaiDishesNum'] += (int)$order->num;
                        if ($order->productType == WaiMaiOrderDetailModel::PRODUCT_TYPE_2) {
                            $income[$date]['boxesTurnover']      += (int)$order->originalPrice;
                            $income[$date]['boxesIncome']        += (int)$order->incomes;
                            $income[$date]['boxesDiscountPrice'] += ((int)$order->originalPrice - (int)$order->incomes);
                        }
                    }
                });

            if ($query->count() == 0) {
                break;
            }

            $id = $query->last()->id;
        }

        Log::info("WaiMaiOrderDetailCHModel 耗时：" . (microtime(true) - $start) . "秒");
        $start = microtime(true);

//        WaiMaiOrderDetailModel::suffix($shopId)

        $customPaymentPayNo         = [];  //流水单号
        $customPaymentDiscountPayNo = [];  //退款单号
        $customPaymentInfo          = [];  //自定义支付信息
        $customPaymentDiscountInfo  = [];  //自定义支付优惠信息
//        PayStreamModel::suffix($shopId, PayStreamModel::SYS_TYPE_RMS_MEAL)

        $streamCustomPayments = CustomPaymentModel::query()->pluck('id')->toArray();

        collect($normalOrder)->chunk(1000)->each(function ($orderIds) use (
            $final,
            $shopId,
            $didNotPay,
            $streamCustomPayments,
            &$income,
            &$customPayment,
            &$customPaymentDiscount,
            &$customPaymentPayNo,
            &$customPaymentDiscountPayNo,
            &$customPaymentInfo,
            &$customPaymentDiscountInfo
        ) {
            PayStreamCHModel::query()
                            ->when($final, function ($query) {
                                $query->final();
                            })
                            ->where('shopId', $shopId)
                            ->whereIn('sysType',
                                [PayStreamModel::SYS_TYPE_RMS_MEAL, PayStreamModel::SYS_TYPE_RMS_COUPON_PACKAGE])
                            ->whereIn('orderId', $orderIds->values()->toArray())
                            ->where('payStatus', CodeEnum::PAY_STATUS_NORMAL)
                            ->select(['createdAt', 'orderId', 'payChannel', 'payPrice', 'payNo', 'isPaidIn', 'shopId'])
                            ->orderBy('payNo')
                            ->get()
                            ->each(function ($order) use (
                                &$income,
                                &$customPayment,
                                &$customPaymentDiscount,
                                &$customPaymentPayNo,
                                &$customPaymentDiscountPayNo,
                                &$customPaymentInfo,
                                &$customPaymentDiscountInfo,
                                $didNotPay,
                                $streamCustomPayments
                            ) {
                                if ($order->createdAt instanceof Carbon) {
                                    $date = $order->createdAt->toDateString();
                                } else {
                                    $date = Carbon::createFromTimestamp($order->createdAt)
                                                  ->toDateString();
                                }
                                if (!isset($income[$date])) {
                                    return;
                                }
                                if (!isset($didNotPay[$date])) {
                                    return;
                                }
                                if (in_array($order->orderId, $didNotPay[$date])) {
                                    if ($order->payChannel == CodeEnum::PAY_CHANNEL_3) {
                                        $income[$date]['cashOrigin'] += (int)$order->payPrice;
                                    }
                                    if ($order->payChannel == CodeEnum::PAY_CHANNEL_4) {
                                        $income[$date]['fuyouOrigin'] += (int)$order->payPrice;
                                    }
                                    if ($order->payChannel > 100 && in_array($order->payChannel,
                                            $streamCustomPayments)) {
                                        foreach ($customPayment as $k => $v) {
                                            if ($order->isPaidIn == PayStreamModel::PAID_IN_YES && $order->payChannel == $v['id']) {
                                                $income[$date]['customPayment'][$k]['amount'] += (int)$order->payPrice;
                                                $customPaymentInfo[$date]['amount']           = (($customPaymentInfo[$date]['amount'] ?? 0) + (int)$order->payPrice);
                                                $customPaymentPayNo[$date][]                  = $order->payNo;
                                            }
                                        }
                                        foreach ($customPaymentDiscount as $k => $v) {
                                            if ($order->isPaidIn == PayStreamModel::PAID_IN_NO && $order->payChannel == $v['id']) {
                                                $income[$date]['customPaymentDiscount'][$k]['amount'] += (int)$order->payPrice;
                                                $customPaymentDiscountInfo[$date]['amount']           = (($customPaymentDiscountInfo[$date]['amount'] ?? 0) + (int)$order->payPrice);
                                                $customPaymentDiscountPayNo[$date][]                  = $order->payNo;
                                            }
                                        }
                                    }
                                }
                            });
        });

        $page = 0;
        while (true) {



            if ($query->count() == 0) {
                break;
            }

            $page++;
        }

        Log::info("PayStreamCHModel 耗时：" . (microtime(true) - $start) . "秒");
        $start = microtime(true);

        OrderExternalCHModel::query()
            ->when($final, function ($query) {
                $query->final();
            })
            ->where('shopId', $shopId)
            ->whereBetween('createdAt', $time['time'])
            ->chunk(1000, function ($orders) use (&$income, $didNotPay) {
                foreach ($orders as $order) {
                    if ($order->createdAt instanceof Carbon) {
                        $date = $order->createdAt->toDateString();
                    } else {
                        $date = Carbon::createFromTimestamp($order->createdAt)
                            ->toDateString();
                    }
                    if (!isset($income[$date])) {
                        continue;
                    }
                    if (!isset($didNotPay[$date])) {
                        continue;
                    }
                    if (in_array($order->orderId, $didNotPay[$date])) {
                        $income[$date]['fuyouOrigin'] -= $order->payPrice;
                    }
                }
            });
        RefundStreamModel::query()
            ->where('shopId', $shopId)
            ->where('refundStatus', RefundStreamModel::REFUND_STATUS_OK)
            ->whereBetween('createdAt', $time['time'])
            ->chunk(1000,
                function ($orders) use (
                    &$income,
                    &$customPayment,
                    &$customPaymentDiscount,
                    $customPaymentPayNo,
                    $customPaymentDiscountPayNo,
                    &$customPaymentInfo,
                    &$customPaymentDiscountInfo
                ) {
                    foreach ($orders as $order) {
                        if ($order->createdAt instanceof Carbon) {
                            $date = $order->createdAt->toDateString();
                        } else {
                            $date = Carbon::createFromTimestamp($order->createdAt)
                                ->toDateString();
                        }
                        if (in_array($order->payNo, $customPaymentPayNo[$date] ?? [])) {
                            foreach ($customPayment as $k => $v) {
                                if ($order->refundChannel == $v['id']) {
                                    $income[$date]['customPayment'][$k]['refund'] += (int)$order->refundPrice;
                                    $customPaymentInfo[$date]['refund']           = (($customPaymentInfo[$date]['refund'] ?? 0) + (int)$order->refundPrice);
                                }
                            }
                        }
                        if (in_array($order->payNo, $customPaymentDiscountPayNo[$date] ?? [])) {
                            foreach ($customPaymentDiscount as $k => $v) {
                                if ($order->refundChannel == $v['id']) {
                                    $income[$date]['customPaymentDiscount'][$k]['refund'] += (int)$order->refundPrice;
                                    $customPaymentDiscountInfo[$date]['refund']           = (($customPaymentInfo[$date]['refund'] ?? 0) + (int)$order->refundPrice);
                                }
                            }
                        }
                    }
                });
        $customPaymentIncomeTotal   = []; //自定义支付收入
        $customPaymentDiscountTotal = []; //自定义支付优惠
        foreach ($customPaymentInfo as $k => $item) {
            $customPaymentIncomeTotal[$k] = ($item['amount'] ?? 0) - ($item['refund'] ?? 0);
        }
        foreach ($customPaymentDiscountInfo as $k => $item) {
            $customPaymentDiscountTotal[$k] = ($item['amount'] ?? 0) - ($item['refund'] ?? 0);
        }

        collect($normalOrder)->chunk(1000)->each(function ($orderIds) use (
            &$income,
            &$outCouponIncome,
            &$dyCouponDiscount,
            &$dyCouponIncome,
            &$xhsCouponIncome,
            &$xhsCouponDiscount,
            &$outCouponDiscount,
            $didNotPay,
            $final,
            $shopId
        ) {

            OrderCouponExtCHModel::query()
                                 ->when($final, function ($query) {
                                     $query->final();
                                 })
                                 ->where('shopId', $shopId)
                                 ->whereIn('orderId', $orderIds->values()->toArray())
                                 ->where('couponExtStatus', CodeEnum::ORDER_COUPON_EXT_STATUS_USE)
                                 ->chunk(1000,
                                     function ($orders) use (
                                         &$income,
                                         &$outCouponIncome,
                                         &$dyCouponDiscount,
                                         &$dyCouponIncome,
                                         &$xhsCouponIncome,
                                         &$xhsCouponDiscount,
                                         &$outCouponDiscount,
                                         $didNotPay
                                     ) {
                                         foreach ($orders as $order) {
                                             if ($order->createdAt instanceof Carbon) {
                                                 $date = $order->createdAt->toDateString();
                                             } else {
                                                 $date = Carbon::createFromTimestamp($order->createdAt)
                                                               ->toDateString();
                                             }
                                             if (!isset($income[$date])) {
                                                 continue;
                                             }
                                             if (in_array($order->orderId, $didNotPay[$date])) {
                                                 $couponName = $order->couponExtName;
                                                 if ($order->extPlatform == CouponExtEnum::EXT_PLATFORM_MEITUAN) {
                                                     foreach ($outCouponIncome as $k => $v) {
                                                         if ($v['name'] == $couponName) {
                                                             $income[$date]['incomeGroupBuy'][$k]['amount'] += (int)$order->couponExtCostPrice;
                                                         }
                                                     }
                                                     foreach ($outCouponDiscount as $k => $v) {
                                                         if ($v['name'] == $couponName) {
                                                             $income[$date]['discountGroupBuy'][$k]['amount'] += (int)$order->couponExtDiscountPrice - (int)$order->couponExtCostPrice;
                                                         }
                                                     }
                                                 }
                                                 if ($order->extPlatform == CouponExtEnum::EXT_PLATFORM_DOUYIN) {
                                                     foreach ($dyCouponIncome as $k => $v) {
                                                         if ($v['name'] == $couponName) {
                                                             $income[$date]['dyCouponIncome'][$k]['amount'] += (int)$order->couponExtCostPrice;
                                                         }
                                                     }
                                                     foreach ($dyCouponDiscount as $k => $v) {
                                                         if ($v['name'] == $couponName) {
                                                             $income[$date]['dyCouponDiscount'][$k]['amount'] += (int)$order->couponExtDiscountPrice - (int)$order->couponExtCostPrice;
                                                         }
                                                     }
                                                 }

                                                 if ($order->extPlatform == CouponExtEnum::EXT_PLATFORM_XHS) {
                                                     foreach ($xhsCouponIncome as $k => $v) {
                                                         if ($v['name'] == $couponName) {
                                                             $income[$date]['xhsCouponIncome'][$k]['amount'] += (int)$order->couponExtCostPrice;
                                                         }
                                                     }
                                                     foreach ($xhsCouponDiscount as $k => $v) {
                                                         if ($v['name'] == $couponName) {
                                                             $income[$date]['xhsCouponDiscount'][$k]['amount'] += (int)$order->couponExtDiscountPrice - (int)$order->couponExtCostPrice;
                                                         }
                                                     }
                                                 }
                                             }
                                         }
                                     });
        });

        $lists = [];
        foreach ($income as $key => $item) {
            $refundOrder = RefundStreamModel::query()
                ->where('shopId', $shopId)
                ->where('refundStatus', RefundStreamModel::REFUND_STATUS_OK)
                ->whereBetween('createdAt', [
                    strtotime($item['date']),
                    strtotime('+1day', strtotime($item['date'])) - 1
                ])
                ->get()
                ->toArray();
            $cashRefund  = 0;
            $fuyouRefund = 0;
            $refund      = [];
            foreach ($refundOrder as $k => $value) {
                if (in_array($value['orderId'], ($didNotPay[$key] ?? []))) {
                    $refund[$k]['type']        = $value['refundChannel'];
                    $refund[$k]['refundPrice'] = $value['refundPrice'];
                }
            }
            foreach ($refund as $v) {
                if ($v['type'] == CodeEnum::PAY_CHANNEL_3) {
                    $cashRefund += $v['refundPrice'];
                } else {
                    if ($v['type'] == CodeEnum::PAY_CHANNEL_4) {
                        $fuyouRefund += $v['refundPrice'];
                    }
                }
            }
            $item['fuyouOrigin']   += $item['couponPackageIncome'];
            $plus['cash']          = $item['cashOrigin'] - $cashRefund;
            $plus['fuyou']         = $item['fuyouOrigin'] - $fuyouRefund;
            $plus['incomeTotal']   = $item['cashOrigin'] - $cashRefund + $item['fuyouOrigin'] - $fuyouRefund + $item['elm'] + $item['mt'] + $item['dy'];
            $plus['discountTotal'] = $item['giveFood'] + $item['discount'] + $item['reduction'] + $item['mtDiscountPrice'] + $item['elmDiscountPrice'] + $item['dyDiscountPrice'];
            $plus['turnover']      = $item['hotelTurnover'] + $item['mtTurnover'] + $item['elmTurnover'] + $item['boxesTurnover'] + $item['dyTurnover'];
            $plus['income']        = $item['hotelIncome'] + $item['mtIncome'] + $item['elmIncome'] + $item['dyIncome'];
            $plus['discountPrice'] = $item['hotelDiscountPrice'] + $item['mtDiscountPrice'] + $item['elmDiscountPrice'] + $item['dyDiscountPrice'];
            if ($item['hotelNum'] != 0) {
                $plus['hotelFrontPrice'] = floor($item['hotelTurnover'] / $item['hotelNum']);
                $plus['hotelAfterPrice'] = floor($item['hotelIncome'] / $item['hotelNum']);
            } else {
                $plus['hotelFrontPrice'] = 0;
                $plus['hotelAfterPrice'] = 0;
            }
            if ($item['mtNum'] != 0) {
                $plus['mtFrontPrice'] = floor($item['mtTurnover'] / $item['mtNum']);
                $plus['mtAfterPrice'] = floor($item['mtIncome'] / $item['mtNum']);
            } else {
                $plus['mtFrontPrice'] = 0;
                $plus['mtAfterPrice'] = 0;
            }
            if ($item['elmNum'] != 0) {
                $plus['elmFrontPrice'] = floor($item['elmTurnover'] / $item['elmNum']);
                $plus['elmAfterPrice'] = floor($item['elmIncome'] / $item['elmNum']);
            } else {
                $plus['elmFrontPrice'] = 0;
                $plus['elmAfterPrice'] = 0;
            }
            if ($item['dyNum'] != 0) {
                $plus['dyFrontPrice'] = floor($item['dyTurnover'] / $item['dyNum']);
                $plus['dyAfterPrice'] = floor($item['dyIncome'] / $item['dyNum']);
            } else {
                $plus['dyFrontPrice'] = 0;
                $plus['dyAfterPrice'] = 0;
            }
            $plus['num'] = $item['hotelNum'] + $item['mtNum'] + $item['elmNum'] + $item['dyNum'];
            if (($item['hotelNum'] + $item['mtNum'] + $item['elmNum'] + $item['dyNum']) != 0) {
                $plus['frontPrice'] = floor(($item['hotelTurnover'] + $item['mtTurnover'] + $item['elmTurnover'] + $item['dyTurnover']) /
                    ($item['hotelNum'] + $item['mtNum'] + $item['elmNum'] + $item['dyNum']));
                $plus['afterPrice'] = floor(($item['hotelIncome'] + $item['mtIncome'] + $item['elmIncome'] + $item['dyIncome']) /
                    ($item['hotelNum'] + $item['mtNum'] + $item['elmNum'] + $item['dyNum']));
            } else {
                $plus['frontPrice'] = 0;
                $plus['afterPrice'] = 0;
            }
            $plus['saleTurnover']      = $item['hotelTurnover'] + $item['mtTurnover'] + $item['elmTurnover'] + $item['dyTurnover'];
            $plus['saleIncome']        = $item['hotelIncome'] + $item['mtIncome'] + $item['elmIncome'] - $item['boxesIncome'] + $item['dyIncome'];
            $plus['saleDiscountPrice'] = $item['hotelDiscountPrice'] + $item['mtDiscountPrice'] + $item['elmDiscountPrice'] + $item['dyDiscountPrice'] - $item['boxesDiscountPrice'];
            $plus['dishes']            = $item['orderDishesNum'] + $item['waimaiDishesNum'];
            if (count($item['incomeGroupBuy'])) {
                foreach ($item['incomeGroupBuy'] as $v) {
                    $plus[$v['key']]     = $v['amount'];
                    $plus['incomeTotal'] += $v['amount'];
                }
            }
            if (count($item['discountGroupBuy'])) {
                foreach ($item['discountGroupBuy'] as $v) {
                    $plus[$v['key']]       = $v['amount'];
                    $plus['discountTotal'] += $v['amount'];
                }
            }
            if (count($item['dyCouponIncome'])) {
                foreach ($item['dyCouponIncome'] as $v) {
                    $plus[$v['key']]     = $v['amount'];
                    $plus['incomeTotal'] += $v['amount'];
                }
            }
            if (count($item['dyCouponDiscount'])) {
                foreach ($item['dyCouponDiscount'] as $v) {
                    $plus[$v['key']]       = $v['amount'];
                    $plus['discountTotal'] += $v['amount'];
                }
            }

            if (count($item['xhsCouponIncome'])) {
                foreach ($item['xhsCouponIncome'] as $v) {
                    $plus[$v['key']]     = $v['amount'];
                    $plus['incomeTotal'] += $v['amount'];
                }
            }
            if (count($item['xhsCouponDiscount'])) {
                foreach ($item['xhsCouponDiscount'] as $v) {
                    $plus[$v['key']]       = $v['amount'];
                    $plus['discountTotal'] += $v['amount'];
                }
            }

            if (count($item['internalCoupon'])) {
                foreach ($item['internalCoupon'] as $v) {
                    $plus[$v['key']]       = $v['amount'];
                    $plus['discountTotal'] += $v['amount'];
                }
            }
            if (count($item['insideCouponIncome'])) {
                foreach ($item['insideCouponIncome'] as $v) {
                    $plus[$v['key']]     = $v['amount'];
                    $plus['incomeTotal'] += $v['amount'];
                }
            }
            if (count($item['customPayment'])) {
                foreach ($item['customPayment'] as $v) {
                    $plus[$v['key']]     = ($v['amount'] - $v['refund']);
                    $plus['incomeTotal'] += ($v['amount'] - $v['refund']);
                }
            }
            if (count($item['customPaymentDiscount'])) {
                foreach ($item['customPaymentDiscount'] as $v) {
                    $plus[$v['key']]       = ($v['amount'] - $v['refund']);
                    $plus['discountTotal'] += ($v['amount'] - $v['refund']);
                }
            }
            if (($item['hotelTurnover'] + $item['mtTurnover'] + $item['elmTurnover'] + $item['dyTurnover']) > 0) {
                $plus['normal'] = 1;
            } else {
                $plus['normal'] = 0;
            }
            $lists[] = array_merge($item, $plus);
        }
        $total                     = [
            "hotelTurnover"              => 0,
            "hotelIncome"                => 0,
            "hotelDiscountPrice"         => 0,
            "hotelNum"                   => 0,
            "hotelFrontPrice"            => 0,
            "hotelAfterPrice"            => 0,
            "mtTurnover"                 => 0,
            "mtIncome"                   => 0,
            "mtDiscountPrice"            => 0,
            "mtNum"                      => 0,
            "mtFrontPrice"               => 0,
            "mtAfterPrice"               => 0,
            "elmTurnover"                => 0,
            "elmIncome"                  => 0,
            "elmDiscountPrice"           => 0,
            "elmNum"                     => 0,
            "couponPackageTurnover"      => 0,
            "couponPackageIncome"        => 0,
            "couponPackageDiscountPrice" => 0,
            "couponPackageNum"           => 0,
            "elmFrontPrice"              => 0,
            "elmAfterPrice"              => 0,
            "boxesTurnover"              => 0,
            "boxesIncome"                => 0,
            "boxesDiscountPrice"         => 0,
            "cash"                       => 0,
            "fuyou"                      => 0,
            "elm"                        => 0,
            "mt"                         => 0,
            "giveFood"                   => 0,
            "discount"                   => 0,
            "reduction"                  => 0,
            "orderDishesNum"             => 0,
            "waimaiDishesNum"            => 0,
            "incomeTotal"                => 0,
            "discountTotal"              => 0,
            "turnover"                   => 0,
            "income"                     => 0,
            "discountPrice"              => 0,
            "num"                        => 0,
            "frontPrice"                 => 0,
            "afterPrice"                 => 0,
            "saleTurnover"               => 0,
            "saleIncome"                 => 0,
            "saleDiscountPrice"          => 0,
            "dishes"                     => 0,
            "normal"                     => 0,
            'mtActivityExpenditure'      => 0,
            'mtPlatformPrice'            => 0,
            'mtOtherExpenses'            => 0,
            'elmActivityExpenditure'     => 0,
            'elmPlatformPrice'           => 0,
            'elmOtherExpenses'           => 0,
            'mtPackingPrice'             => 0,
            'mtShippingPrice'            => 0,
            'elmPackingPrice'            => 0,
            'elmShippingPrice'           => 0,
            "dyTurnover"                 => 0,
            "dyIncome"                   => 0,
            "dyDiscountPrice"            => 0,
            "dyNum"                      => 0,
            "dyFrontPrice"               => 0,
            "dyAfterPrice"               => 0,
            "dy"                         => 0,
            'dyActivityExpenditure'      => 0,
            'dyPlatformPrice'            => 0,
            'dyOtherExpenses'            => 0,
            'dyPackingPrice'             => 0,
            'dyShippingPrice'            => 0,
        ];
        $outCouponIncomePush       = [];
        $outCouponDiscountPush     = [];
        $dyCouponIncomePush        = [];
        $dyCouponDiscountPush      = [];
        $xhsCouponIncomePush        = [];
        $xhsCouponDiscountPush      = [];
        $insideCouponNamePush      = [];
        $insideCouponIncomePush    = [];
        $customPaymentPush         = [];
        $customPaymentDiscountPush = [];
        foreach ($outCouponIncome as $v) {
            $outCouponIncomePush[$v['key']] = 0;
        }
        foreach ($outCouponDiscount as $v) {
            $outCouponDiscountPush[$v['key']] = 0;
        }
        foreach ($dyCouponIncome as $v) {
            $dyCouponIncomePush[$v['key']] = 0;
        }
        foreach ($dyCouponDiscount as $v) {
            $dyCouponDiscountPush[$v['key']] = 0;
        }
        foreach ($xhsCouponIncome as $v) {
            $xhsCouponIncomePush[$v['key']] = 0;
        }
        foreach ($xhsCouponDiscount as $v) {
            $xhsCouponDiscountPush[$v['key']] = 0;
        }
        foreach ($insideCouponName as $v) {
            $insideCouponNamePush[$v['key']] = 0;
        }
        foreach ($insideCouponIncome as $v) {
            $insideCouponIncomePush[$v['key']] = 0;
        }
        foreach ($customPayment as $v) {
            $customPaymentPush[$v['key']] = 0;
        }
        foreach ($customPaymentDiscount as $v) {
            $customPaymentDiscountPush[$v['key']] = 0;
        }
        $total = array_merge($total, $outCouponIncomePush, $outCouponDiscountPush, $insideCouponNamePush,
            $insideCouponIncomePush, $customPaymentPush, $customPaymentDiscountPush, $dyCouponIncomePush, $dyCouponDiscountPush,
        $xhsCouponIncomePush, $xhsCouponDiscountPush
        );
        foreach ($lists as $list) {
            $total['hotelTurnover']              += $list['hotelTurnover'];
            $total['hotelIncome']                += $list['hotelIncome'];
            $total['hotelDiscountPrice']         += $list['hotelDiscountPrice'];
            $total['hotelNum']                   += $list['hotelNum'];
            $total['mtTurnover']                 += $list['mtTurnover'];
            $total['mtIncome']                   += $list['mtIncome'];
            $total['mtDiscountPrice']            += $list['mtDiscountPrice'];
            $total['mtNum']                      += $list['mtNum'];
            $total['elmTurnover']                += $list['elmTurnover'];
            $total['elmIncome']                  += $list['elmIncome'];
            $total['elmDiscountPrice']           += $list['elmDiscountPrice'];
            $total['elmNum']                     += $list['elmNum'];
            $total['couponPackageTurnover']      += $list['couponPackageTurnover'];
            $total['couponPackageIncome']        += $list['couponPackageIncome'];
            $total['couponPackageDiscountPrice'] += $list['couponPackageDiscountPrice'];
            $total['couponPackageNum']           += $list['couponPackageNum'];
            $total['boxesTurnover']              += $list['boxesTurnover'];
            $total['boxesIncome']                += $list['boxesIncome'];
            $total['boxesDiscountPrice']         += $list['boxesDiscountPrice'];
            $total['cash']                       += $list['cash'];
            $total['fuyou']                      += $list['fuyou'];
            $total['elm']                        += $list['elm'];
            $total['mt']                         += $list['mt'];
            $total['giveFood']                   += $list['giveFood'];
            $total['discount']                   += $list['discount'];
            $total['reduction']                  += $list['reduction'];
            $total['orderDishesNum']             += $list['orderDishesNum'];
            $total['waimaiDishesNum']            += $list['waimaiDishesNum'];
            $total['incomeTotal']                += $list['incomeTotal'];
            $total['discountTotal']              += $list['discountTotal'];
            $total['turnover']                   += $list['turnover'];
            $total['income']                     += $list['income'];
            $total['discountPrice']              += $list['discountPrice'];
            $total['num']                        += $list['num'];
            $total['saleTurnover']               += $list['saleTurnover'];
            $total['saleIncome']                 += $list['saleIncome'];
            $total['saleDiscountPrice']          += $list['saleDiscountPrice'];
            $total['dishes']                     += $list['dishes'];
            $total['normal']                     += $list['normal'];
            $total['elmActivityExpenditure']     += $list['elmActivityExpenditure'];
            $total['elmPlatformPrice']           += $list['elmPlatformPrice'];
            $total['elmOtherExpenses']           += $list['elmOtherExpenses'];
            $total['mtActivityExpenditure']      += $list['mtActivityExpenditure'];
            $total['mtPlatformPrice']            += $list['mtPlatformPrice'];
            $total['mtOtherExpenses']            += $list['mtOtherExpenses'];
            $total['elmShippingPrice']           += $list['elmShippingPrice'];
            $total['elmPackingPrice']            += $list['elmPackingPrice'];
            $total['mtShippingPrice']            += $list['mtShippingPrice'];
            $total['mtPackingPrice']             += $list['mtPackingPrice'];
            $total['dyTurnover']                 += $list['dyTurnover'];
            $total['dyIncome']                   += $list['dyIncome'];
            $total['dyDiscountPrice']            += $list['dyDiscountPrice'];
            $total['dyNum']                      += $list['dyNum'];
            $total['dy']                         += $list['dy'];
            $total['dyActivityExpenditure']      += $list['dyActivityExpenditure'];
            $total['dyPlatformPrice']            += $list['dyPlatformPrice'];
            $total['dyOtherExpenses']            += $list['dyOtherExpenses'];
            $total['dyShippingPrice']            += $list['dyShippingPrice'];
            $total['dyPackingPrice']             += $list['dyPackingPrice'];
            if (count($list['incomeGroupBuy'])) {
                foreach ($list['incomeGroupBuy'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }
            if (count($list['discountGroupBuy'])) {
                foreach ($list['discountGroupBuy'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }
            if (count($list['dyCouponIncome'])) {
                foreach ($list['dyCouponIncome'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }
            if (count($list['dyCouponDiscount'])) {
                foreach ($list['dyCouponDiscount'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }

            if (count($list['xhsCouponIncome'])) {
                foreach ($list['xhsCouponIncome'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }
            if (count($list['xhsCouponDiscount'])) {
                foreach ($list['xhsCouponDiscount'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }

            if (count($list['internalCoupon'])) {
                foreach ($list['internalCoupon'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }
            if (count($list['insideCouponIncome'])) {
                foreach ($list['insideCouponIncome'] as $v) {
                    $total[$v['key']] += $v['amount'];
                }
            }
            if (count($list['customPayment'])) {
                foreach ($list['customPayment'] as $v) {
                    $total[$v['key']] += ($v['amount'] - $v['refund']);
                }
            }
            if (count($list['customPaymentDiscount'])) {
                foreach ($list['customPaymentDiscount'] as $v) {
                    $total[$v['key']] += ($v['amount'] - $v['refund']);
                }
            }
        }
        if ($total['hotelNum'] != 0) {
            $total['hotelFrontPrice'] = floor($total['hotelTurnover'] / $total['hotelNum']);
            $total['hotelAfterPrice'] = floor($total['hotelIncome'] / $total['hotelNum']);
        } else {
            $total['hotelFrontPrice'] = 0;
            $total['hotelAfterPrice'] = 0;
        }
        if ($total['mtNum'] != 0) {
            $total['mtFrontPrice'] = floor($total['mtTurnover'] / $total['mtNum']);
            $total['mtAfterPrice'] = floor($total['mtIncome'] / $total['mtNum']);
        } else {
            $total['mtFrontPrice'] = 0;
            $total['mtAfterPrice'] = 0;
        }
        if ($total['elmNum'] != 0) {
            $total['elmFrontPrice'] = floor($total['elmTurnover'] / $total['elmNum']);
            $total['elmAfterPrice'] = floor($total['elmIncome'] / $total['elmNum']);
        } else {
            $total['elmFrontPrice'] = 0;
            $total['elmAfterPrice'] = 0;
        }
        if ($total['dyNum'] != 0) {
            $total['dyFrontPrice'] = floor($total['dyTurnover'] / $total['dyNum']);
            $total['dyAfterPrice'] = floor($total['dyIncome'] / $total['dyNum']);
        } else {
            $total['dyFrontPrice'] = 0;
            $total['dyAfterPrice'] = 0;
        }
        if ($total['num'] != 0) {
            $total['frontPrice'] = floor($total['turnover'] / $total['num']);
            $total['afterPrice'] = floor($total['income'] / $total['num']);
        } else {
            $total['frontPrice'] = 0;
            $total['afterPrice'] = 0;
        }
        $info = [
            'outCouponIncome'       => $outCouponIncome,
            'outCouponDiscount'     => $outCouponDiscount,
            'dyCouponIncome'        => $dyCouponIncome,
            'dyCouponDiscount'      => $dyCouponDiscount,
            'xhsCouponIncome'        => $xhsCouponIncome,
            'xhsCouponDiscount'      => $xhsCouponDiscount,
            'inCoupon'              => $insideCouponName,
            'insideCouponIncome'    => $insideCouponIncome,
            'customPayment'         => $customPayment,
            'customPaymentDiscount' => $customPaymentDiscount,
            'lists'                 => $lists,
            'total'                 => $total
        ];
        Cache::store('redis')->tags($tag)->put($redisKey, $info, strtotime(date('Y-m-d') . ' 23:59:59') - time());
        return $info;
    }

    //综合营业统计导出
    public static function comprehensiveStatistics($shopId, $monthDate, $today)
    {
        $data                       = self::comprehensiveList($shopId, $monthDate, $today);
        $outCouponCount             = count($data['outCouponIncome']);
        $dyCouponCount              = count($data['dyCouponIncome']);
        $xhsCouponCount              = count($data['xhsCouponIncome']);
        $inCouponCount              = count($data['inCoupon']);
        $customPaymentCount         = count($data['customPayment']);
        $customPaymentDiscountCount = count($data['customPaymentDiscount']);
        if ($shopId) {
            $shop     = Shop::find($shopId);
            $shopName = (string)optional($shop)->name;
            $shopCode = (string)optional($shop)->shopCode;
        } else {
            throw new StatisticsException(StatisticsCodeEnum::ERR_QUERY_DATA_EMPTY, '参数不正确');
        }

        if (config('app.env') == 'qa') {
            $time = BaseService::getMonthTime($monthDate, 1);
        } else {
            $time = BaseService::getMonthTime($monthDate, $today);
        }
        if (!count($time['time'])) {
            throw new StatisticsException(StatisticsCodeEnum::ERR_QUERY_DATA_EMPTY, '参数不正确');
        }
        $outCoupon = [];
        foreach ($data['outCouponIncome'] as $v) {
            $outCoupon[] = $v['name'];
        }
        $inCoupon = [];
        foreach ($data['inCoupon'] as $v) {
            $inCoupon[] = $v['name'];
        }
        $dyCoupon = [];
        foreach ($data['dyCouponIncome'] as $v) {
            $dyCoupon[] = $v['name'];
        }
        $xhsCoupon = [];
        foreach ($data['xhsCouponIncome'] as $v) {
            $xhsCoupon[] = $v['name'];
        }
        $customPayment = [];
        foreach ($data['customPayment'] as $v) {
            $customPayment[] = $v['name'];
        }
        $customPaymentDiscount = [];
        foreach ($data['customPaymentDiscount'] as $v) {
            $customPaymentDiscount[] = $v['name'];
        }
        $cellA2Value = '统计方式：【结账方式】；统计周期：【按天】；按门店统计【是】；营业日期：【' . date('Y-m-d', $time['time'][0]) . '至' . date('Y-m-d',
                $time['time'][1]) . '】；门店：' . $shopName . '；订单分类：【全部】；订单来源：【全部】';
        $spreadsheet = new Spreadsheet();
        $sheet       = $spreadsheet->getActiveSheet();
        $countTotal  = 0;
        foreach ($data['lists'] as $item) {
            $countTotal = count($item) - 7;
        }
        $sheet->setCellValue('A1', '综合营业统计');
        $sheet->mergeCells('A1:' . Coordinate::stringFromColumnIndex($countTotal) . '1');
        $sheet->setCellValue('A2', $cellA2Value);
        $sheet->mergeCells('A2:' . Coordinate::stringFromColumnIndex($countTotal) . '2');
        $sheet->setCellValue('A3', '营业日期');
        $sheet->mergeCells('A3:A5');
        $sheet->setCellValue('B3', '营业天数');
        $sheet->mergeCells('B3:B5');
        $sheet->setCellValue('C3', '餐饮销售营业统计');
        $sheet->mergeCells('C3:H4');
        $sheet->setCellValue('I3', '渠道营业构成');
        $sheet->mergeCells('I3:AB3');
        $sheet->setCellValue('I4', '店内销售');
        $sheet->mergeCells('I4:N4');
        $sheet->setCellValue('O4', '美团外卖');
        $sheet->mergeCells('O4:U4');
        $sheet->setCellValue('V4', '饿了么外卖');
        $sheet->mergeCells('V4:AB4');
        $sheet->setCellValue('AC3', '销售品项收入构成');
        $sheet->mergeCells('AC3:AI3');
        $sheet->setCellValue('AC4', '菜品销售');
        $sheet->mergeCells('AC4:AF4');
        $sheet->setCellValue('AG4', '餐盒费');
        $sheet->mergeCells('AG4:AI4');
        $sheet->setCellValue('AJ3', '权益出让营业统计');
        $sheet->mergeCells('AJ3:AM3');
        $sheet->setCellValue('AJ4', '券包销售');
        $sheet->mergeCells('AJ4:AM4');
        $sheet->setCellValue('AN3', '营业收入构成');
        $sheet->mergeCells('AN3:' . Coordinate::stringFromColumnIndex(44 + ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)) . '3');
        $sheet->setCellValue('AN4', '现金');
        $sheet->setCellValue('AO4', '扫码支付');
        $sheet->setCellValue('AP4', '美团/大众点评');
        $column = 41;
        if ($outCouponCount) {
            foreach ($outCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                    DataType::TYPE_STRING);
            }
        } else {
            $sheet->setCellValue('AP5', '--');
            $column++;
        }
        $sheet->mergeCells('AP4:' . Coordinate::stringFromColumnIndex($column) . '4'); // 美团  大众点评 子优惠券

        //------------- 抖音团购 -----------
        $startColumn = $column+1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4', '抖音团购');
        if ($dyCouponCount) {
            foreach ($dyCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item, DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5', '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        //------------- 小红书券码核销 -----------
        $startColumn = $column+1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4', '小红书券码核销');
        if ($xhsCouponCount) {
            foreach ($xhsCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item, DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5', '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        //------------- 店内活动 -----------
        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4', '店内活动');
        if ($inCouponCount) {
            foreach ($inCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item, DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5', '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        //------------- 自定义支付 -----------
        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4', '自定义支付');
        if ($customPaymentCount) {
            foreach ($customPayment as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                    DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5', '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        //------------- 外卖 -----------
        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '外卖');
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column+2) . '4');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5',
            '美团外卖');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5',
            '饿了么外卖');

        //------------- 小计 -----------
        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '小计');
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex(++$column) . '5');

        $startColumn = $column+1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '3',
            '支付优惠构成');
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '3:' . Coordinate::stringFromColumnIndex($column+1) . '3');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '美团/大众点评');
        if ($outCouponCount) {
            foreach ($outCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                    DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5',
                '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4', '抖音团购');
        if ($dyCouponCount) {
            foreach ($dyCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                    DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5',
                '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4', '小红书券码核销');
        if ($xhsCouponCount) {
            foreach ($xhsCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                    DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5',
                '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4', '店内活动');
        if ($inCouponCount) {
            foreach ($inCoupon as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                    DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5', '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '手动记账');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5', '赠菜');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5', '减免');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5', '打折');
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '自定义支付');
        if ($customPaymentDiscountCount) {
            foreach ($customPaymentDiscount as $item) {
                $column++;
                $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                    DataType::TYPE_STRING);
            }
        } else {
            $column++;
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . '5',
                '--');
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '美团外卖');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5', '商家活动支出');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5', '平台服务费');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5', '其他支出');
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '饿了么外卖');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5',
            '商家活动支出');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5',
            '平台服务费');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex(++$column) . '5',
            '其他支出');
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex($column) . '4');

        $startColumn = $column + 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColumn) . '4',
            '小计');
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColumn) . '4:' . Coordinate::stringFromColumnIndex(++$column) . '5');
        $rows   = [
            '营业额(含餐盒费)',
            '优惠金额(含餐盒费)',
            '营业收入(含餐盒费)',
            '订单量',
            '折前单均',
            '折后单均',
            '营业额',
            '优惠金额',
            '营业收入',
            '订单量',
            '折前单均',
            '折后单均',
            '营业额',
            '餐盒费',
            '优惠金额',
            '营业收入',
            '订单量',
            '折前单均',
            '折后单均',
            '营业额',
            '餐盒费',
            '优惠金额',
            '营业收入',
            '订单量',
            '折前单均',
            '折后单均',
            '营业额',
            '优惠金额',
            '营业收入',
            '菜品销量',
            '营业额',
            '优惠金额',
            '营业收入',
            '营业额',
            '优惠金额',
            '营业收入',
            '券包销量',
            '人民币',
            '富友'
        ];
        $column = 3;
        foreach ($rows as $item) {
            $sheet->setCellValueExplicit(Coordinate::stringFromColumnIndex($column) . '5', $item,
                DataType::TYPE_STRING);
            $column++;
        }
        $raws = [];
        foreach ($data['lists'] as $list) {
            $strip[0]  = $list['date'];
            $strip[1]  = $list['normal'];
            $strip[2]  = $list['turnover'] / 100;
            $strip[3]  = $list['discountPrice'] / 100;
            $strip[4]  = $list['income'] / 100;
            $strip[5]  = $list['num'];
            $strip[6]  = $list['frontPrice'] / 100;
            $strip[7]  = $list['afterPrice'] / 100;
            $strip[8]  = $list['hotelTurnover'] / 100;
            $strip[9]  = $list['hotelDiscountPrice'] / 100;
            $strip[10] = $list['hotelIncome'] / 100;
            $strip[11] = $list['hotelNum'];
            $strip[12] = $list['hotelFrontPrice'] / 100;
            $strip[13] = $list['hotelAfterPrice'] / 100;
            $strip[14] = $list['mtTurnover'] / 100;
            $strip[15] = $list['mtPackingPrice'] / 100;
            $strip[16] = $list['mtDiscountPrice'] / 100;
            $strip[17] = $list['mtIncome'] / 100;
            $strip[18] = $list['mtNum'];
            $strip[19] = $list['mtFrontPrice'] / 100;
            $strip[20] = $list['mtAfterPrice'] / 100;
            $strip[21] = $list['elmTurnover'] / 100;
            $strip[22] = $list['elmPackingPrice'] / 100;
            $strip[23] = $list['elmDiscountPrice'] / 100;
            $strip[24] = $list['elmIncome'] / 100;
            $strip[25] = $list['elmNum'];
            $strip[26] = $list['elmFrontPrice'] / 100;
            $strip[27] = $list['elmAfterPrice'] / 100;
            $strip[28] = $list['saleTurnover'] / 100;
            $strip[29] = $list['saleDiscountPrice'] / 100;
            $strip[30] = $list['saleIncome'] / 100;
            $strip[31] = $list['dishes'];
            $strip[32] = $list['boxesTurnover'] / 100;
            $strip[33] = $list['boxesDiscountPrice'] / 100;
            $strip[34] = $list['boxesIncome'] / 100;
            $strip[35] = $list['couponPackageTurnover'] / 100;
            $strip[36] = $list['couponPackageDiscountPrice'] / 100;
            $strip[37] = $list['couponPackageIncome'] / 100;
            $strip[38] = $list['couponPackageNum'];
            $strip[39] = $list['cash'] / 100;
            $strip[40] = $list['fuyou'] / 100;

            if ($outCouponCount) {
                foreach ($outCoupon as $key => $value) {
                    $strip[41 + $key] = $list['couponOutIncome' . $key] / 100;
                }
            } else {
                $strip[41] = 0;
            }

            if ($dyCouponCount) {
                foreach ($dyCoupon as $key => $value) {
                    $strip[41 + ($outCouponCount ?: 1) + $key] = $list['dyCouponIncome' . $key] / 100;
                }
            } else {
                $strip[41 + ($outCouponCount ?: 1)] = 0;
            }

            if ($xhsCouponCount) {
                foreach ($xhsCoupon as $key => $value) {
                    $strip[41 + ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + $key] = $list['xhsCouponIncome' . $key] / 100;
                }
            } else {
                $strip[41 + ($outCouponCount ?: 1) + ($dyCouponCount ?: 1)] = 0;
            }


            if ($inCouponCount) {
                foreach ($inCoupon as $key => $value) {
                    $strip[41 + ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + $key] = $list['couponIncome' . $key] / 100;
                }
            } else {
                $strip[41 + ($dyCouponCount ?: 1) + ($outCouponCount ?: 1) + ($xhsCouponCount ?: 1)] = 0;
            }


            if ($customPaymentCount) {
                foreach ($customPayment as $key => $value) {
                    $strip[41 + ($dyCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($xhsCouponCount ?: 1) + $key] = $list['customPayment' . $key] / 100;
                }
            } else {
                $strip[41 + ($dyCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($xhsCouponCount ?: 1)] = 0;
            }
            $strip[41 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $list['mt'] / 100;
            $strip[42 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $list['elm'] / 100;
            $strip[43 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $list['incomeTotal'] / 100;
            if ($outCouponCount) {
                foreach ($outCoupon as $key => $value) {
                    $strip[44 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $list['couponOutDiscount' . $key] / 100;
                }
            } else {
                $strip[44 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
            }

            if ($dyCouponCount) {
                foreach ($dyCoupon as $key => $value) {
                    $strip[44 + 2 * ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $list['dyCouponDiscount' . $key] / 100;
                }
            } else {
                $strip[44 + 2 * ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
            }

            if ($xhsCouponCount) {
                foreach ($xhsCoupon as $key => $value) {
                    $strip[44 + 2 * ($outCouponCount ?: 1) + 2*($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $list['xhsCouponDiscount' . $key] / 100;
                }
            } else {
                $strip[44 + 2 * ($outCouponCount ?: 1) + 2*($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
            }

            if ($inCouponCount) {
                foreach ($inCoupon as $key => $value) {
                    $strip[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $list['couponIn' . $key] / 100;
                }
            } else {
                $strip[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
            }
            $strip[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $list['giveFood'] / 100;
            $strip[45 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $list['reduction'] / 100;
            $strip[46 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $list['discount'] / 100;
            if ($customPaymentDiscountCount) {
                foreach ($customPaymentDiscount as $key => $value) {
                    $strip[47 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $list['customPaymentDiscount' . $key] / 100;
                }
            } else {
                $strip[47 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
            }
            $strip[47 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $list['mtActivityExpenditure'] / 100;
            $strip[48 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $list['mtPlatformPrice'] / 100;
            $strip[49 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $list['mtOtherExpenses'] / 100;
            $strip[50 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $list['elmActivityExpenditure'] / 100;
            $strip[51 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $list['elmPlatformPrice'] / 100;
            $strip[52 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $list['elmOtherExpenses'] / 100;
            $strip[53 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $list['discountTotal'] / 100;
            $raws[]                                                                                                                               = $strip;
        }
        foreach ($raws as $k => $raw) {
            $column = 1;
            foreach ($raw as $value) {
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($column) . ($k + 6), $value);
                $column++;
            }
        }
        $totalCount[1]  = $data['total']['normal'];
        $totalCount[2]  = $data['total']['turnover'] / 100;
        $totalCount[3]  = $data['total']['discountPrice'] / 100;
        $totalCount[4]  = $data['total']['income'] / 100;
        $totalCount[5]  = $data['total']['num'];
        $totalCount[6]  = $data['total']['frontPrice'] / 100;
        $totalCount[7]  = $data['total']['afterPrice'] / 100;
        $totalCount[8]  = $data['total']['hotelTurnover'] / 100;
        $totalCount[9]  = $data['total']['hotelDiscountPrice'] / 100;
        $totalCount[10] = $data['total']['hotelIncome'] / 100;
        $totalCount[11] = $data['total']['hotelNum'];
        $totalCount[12] = $data['total']['hotelFrontPrice'] / 100;
        $totalCount[13] = $data['total']['hotelAfterPrice'] / 100;
        $totalCount[14] = $data['total']['mtTurnover'] / 100;
        $totalCount[15] = $data['total']['mtPackingPrice'] / 100;
        $totalCount[16] = $data['total']['mtDiscountPrice'] / 100;
        $totalCount[17] = $data['total']['mtIncome'] / 100;
        $totalCount[18] = $data['total']['mtNum'];
        $totalCount[19] = $data['total']['mtFrontPrice'] / 100;
        $totalCount[20] = $data['total']['mtAfterPrice'] / 100;
        $totalCount[21] = $data['total']['elmTurnover'] / 100;
        $totalCount[22] = $data['total']['elmPackingPrice'] / 100;
        $totalCount[23] = $data['total']['elmDiscountPrice'] / 100;
        $totalCount[24] = $data['total']['elmIncome'] / 100;
        $totalCount[25] = $data['total']['elmNum'];
        $totalCount[26] = $data['total']['elmFrontPrice'] / 100;
        $totalCount[27] = $data['total']['elmAfterPrice'] / 100;
        $totalCount[28] = $data['total']['saleTurnover'] / 100;
        $totalCount[29] = $data['total']['saleDiscountPrice'] / 100;
        $totalCount[30] = $data['total']['saleIncome'] / 100;
        $totalCount[31] = $data['total']['dishes'];
        $totalCount[32] = $data['total']['boxesTurnover'] / 100;
        $totalCount[33] = $data['total']['boxesDiscountPrice'] / 100;
        $totalCount[34] = $data['total']['boxesIncome'] / 100;
        $totalCount[35] = $data['total']['couponPackageTurnover'] / 100;
        $totalCount[36] = $data['total']['couponPackageDiscountPrice'] / 100;
        $totalCount[37] = $data['total']['couponPackageIncome'] / 100;
        $totalCount[38] = $data['total']['couponPackageNum'];
        $totalCount[39] = $data['total']['cash'] / 100;
        $totalCount[40] = $data['total']['fuyou'] / 100;
        if ($outCouponCount) {
            foreach ($outCoupon as $key => $value) {
                $totalCount[41 + $key] = $data['total']['couponOutIncome' . $key] / 100;
            }
        } else {
            $totalCount[41] = 0;
        }

        if ($dyCouponCount) {
            foreach ($dyCoupon as $key => $value) {
                $totalCount[41 + ($outCouponCount ?: 1) + $key] = $data['total']['dyCouponIncome' . $key] / 100;
            }
        } else {
            $totalCount[41 + ($outCouponCount ?: 1)] = 0;
        }

        if ($xhsCouponCount) {
            foreach ($xhsCoupon as $key => $value) {
                $totalCount[41 + ($dyCouponCount ?: 1) + ($outCouponCount ?: 1) + $key] = $data['total']['xhsCouponIncome' . $key] / 100;
            }
        } else {
            $totalCount[41 + ($dyCouponCount ?: 1) + ($outCouponCount ?: 1)] = 0;
        }

        if ($inCouponCount) {
            foreach ($inCoupon as $key => $value) {
                $totalCount[41 + ($dyCouponCount ?: 1) + ($outCouponCount ?: 1) + ($xhsCouponCount ?: 1) + $key] = $data['total']['couponIncome' . $key] / 100;
            }
        } else {
            $totalCount[41 + ($dyCouponCount ?: 1) + ($outCouponCount ?: 1) + ($xhsCouponCount ?: 1)] = 0;
        }
        if ($customPaymentCount) {
            foreach ($customPayment as $key => $value) {
                $totalCount[41 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + $key] = $data['total']['customPayment' . $key] / 100;
            }
        } else {
            $totalCount[41 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1)] = 0;
        }
        $totalCount[41 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $data['total']['mt'] / 100;
        $totalCount[42 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $data['total']['elm'] / 100;
        $totalCount[43 + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($outCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $data['total']['incomeTotal'] / 100;
        if ($outCouponCount) {
            foreach ($outCoupon as $key => $value) {
                $totalCount[44 + ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $data['total']['couponOutDiscount' . $key] / 100;
            }
        } else {
            $totalCount[44 + ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
        }

        if ($dyCouponCount) {
            foreach ($dyCoupon as $key => $value) {
                $totalCount[44 + 2 * ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $data['total']['dyCouponDiscount' . $key] / 100;
            }
        } else {
            $totalCount[44 + 2 * ($outCouponCount ?: 1) + ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
        }

        if ($xhsCouponCount) {
            foreach ($xhsCoupon as $key => $value) {
                $totalCount[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $data['total']['xhsCouponDiscount' . $key] / 100;
            }
        } else {
            $totalCount[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
        }

        if ($inCouponCount) {
            foreach ($inCoupon as $key => $value) {
                $totalCount[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $data['total']['couponIn' . $key] / 100;
            }
        } else {
            $totalCount[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
        }
        $totalCount[44 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $data['total']['giveFood'] / 100;
        $totalCount[45 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $data['total']['reduction'] / 100;
        $totalCount[46 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = $data['total']['discount'] / 100;
        if ($customPaymentDiscountCount) {
            foreach ($customPaymentDiscount as $key => $value) {
                $totalCount[47 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + $key] = $data['total']['customPaymentDiscount' . $key] / 100;
            }
        } else {
            $totalCount[47 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1)] = 0;
        }
        $totalCount[47 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $data['total']['mtActivityExpenditure'] / 100;
        $totalCount[48 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $data['total']['mtPlatformPrice'] / 100;
        $totalCount[49 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $data['total']['mtOtherExpenses'] / 100;
        $totalCount[50 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $data['total']['elmActivityExpenditure'] / 100;
        $totalCount[51 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $data['total']['elmPlatformPrice'] / 100;
        $totalCount[52 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $data['total']['elmOtherExpenses'] / 100;
        $totalCount[53 + 2 * ($outCouponCount ?: 1) + 2 * ($dyCouponCount ?: 1) + 2 * ($xhsCouponCount ?: 1) + 2 * ($inCouponCount ?: 1) + ($customPaymentCount ?: 1) + ($customPaymentDiscountCount ?: 1)] = $data['total']['discountTotal'] / 100;
        $count                                                                                                                                     = count($data['lists']);
        $column                                                                                                                                    = 1;
        $sheet->setCellValue('A' . ($count + 6), '月统计');
        foreach ($totalCount as $value) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($column + 1) . ($count + 6), $value);
            $column++;
        }
        $tmpFile = sys_get_temp_dir() . "/$shopCode" . "-" . "{$shopName}综合营业统计" . date('Ymd',
                $time['time'][0] ?? time()) . "-" . date('Ymd', $time['time'][0] ?? time()) . '.xlsx';
        IOFactory::createWriter($spreadsheet, 'Xlsx')->save($tmpFile);
        return $tmpFile;
    }

    /**
     * 订单列表数据导出
     * @param $shopId
     * @param $payChannel
     * @param $sourceType
     * @param $completeTimeStart
     * @param $completeTimeEnd
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function shopOrderExcel($shopId, $payChannel, $sourceType, $completeTimeStart, $completeTimeEnd, string $savePath = '', bool $doExcel = true)
    {
        set_time_limit(0);
        @ini_set('memory_limit', '-1');
        $shopInfo       = ShopService::getShopInfo($shopId);
        $storeOrderList = OrderStatisticsService::getOrderMappingData($shopId, $payChannel, $sourceType,
            $completeTimeStart, $completeTimeEnd);
        //获取订单明细
        $orderList = OrderStatisticsService::getInStoreOrderToChange($storeOrderList, $shopInfo);
        //获取菜品明细
        $productList = OrderStatisticsService::getInStoreProductToChange($storeOrderList, $shopInfo);
        //获取支付明细
        $inStorePayStreamRows = OrderStatisticsService::getInStorePayStream($storeOrderList, $shopInfo);
        $inStorePayStream     = self::mergePayStreamData($shopInfo, $inStorePayStreamRows);
        //获取优惠明细
        $discountRows = OrderStatisticsService::getInStoreDiscountData($storeOrderList, $shopInfo);
        $discountData = self::mergeDiscountData($shopInfo, $discountRows);

        $data         = [
            $orderList,
            $productList,
            $inStorePayStream,
            $discountData
        ];
        unset($orderList);
        unset($productList);
        unset($inStorePayStream);
        unset($discountData);
        unset($storeOrderList);
        unset($discountRows);
        unset($inStorePayStreamRows);
        $name = str_pad($shopInfo['shopCode'], ShopCodeEnum::CODE_TEXT[ShopCodeEnum::LEADING_ZERO], "0",
                STR_PAD_LEFT) . '-' . $shopInfo['name'] . '-' . $completeTimeStart . '_' . $completeTimeEnd;

        if ($doExcel) {
            self::spreadsheetExcel($data, $name, $savePath);
        } else {
            return $data;
        }
    }

    public static function spreadsheetExcel($data, $name, string $savePath): void
    {
        $spreadsheet = new Spreadsheet();
        //关闭预处理
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($spreadsheet);
        $writer->setPreCalculateFormulas(false);
        foreach ($data as $key => $value) {
            self::opSheet($spreadsheet, $key, $value);
            unset($value);
        }
        $spreadsheet->removeSheetByIndex(count($data));

        if (!$savePath) {

            // 数据流导出
            header("content-type:application/octet-stream; charset=utf8");
            header("Content-Disposition:attachment;filename=" . urlencode($name) . ".xls");
            header('Cache-Control: max-age=0');
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
            //删除清空：
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            exit;
        } else {

            // 写入指定路径
            $writer = new Xlsx($spreadsheet);
            $writer->save($savePath);
            //删除清空：
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
        }
    }

    public static function mergePayStreamData($shopInfo, $inStorePayStreamRows): array
    {
        $payStreamRows = [
            "门店编号",
            '门店',
            '订单编号',
            '支付方式',
            '支付优惠（元）',
            '实收（元）',
            '支付时间',
            '支付状态',
            '支付商户号',
            '流水号',
            '是否退款',
            '退款金额',
            '操作人',
        ];
        array_unshift($inStorePayStreamRows, $payStreamRows);
        return [
            'title' => '支付明细',
            'info'  => [
                '门店'   => $shopInfo['name'],
                '门店编码' => str_pad($shopInfo['shopCode'],
                    ShopCodeEnum::CODE_TEXT[ShopCodeEnum::LEADING_ZERO], "0", STR_PAD_LEFT),
            ],
            'rows'  => $inStorePayStreamRows
        ];
    }

    public static function mergeDiscountData($shopInfo, $discountRows): array
    {
        $couponRows = ['门店编号', '门店', '订单编号', '优惠类型', '优惠名称', '优惠原因', '优惠金额',];
        array_unshift($discountRows, $couponRows);
        return [
            'title' => '优惠明细',
            'info'  => [
                '门店'   => $shopInfo['name'],
                '门店编码' => str_pad($shopInfo['shopCode'],
                    ShopCodeEnum::CODE_TEXT[ShopCodeEnum::LEADING_ZERO], "0", STR_PAD_LEFT),
            ],
            'rows'  => $discountRows
        ];
    }

    /**
     * 处理多sheet
     * @param [type] $spreadsheet
     * @param [type] $n
     * @param [type] $data
     * @return void
     * @author bwy <xxx@xxx.com>
     */
    public static function opSheet($spreadsheet, $n, $data)
    {
        $items = [];
        foreach ($data['rows'] as $key => $item) {
            if ($key == 0) {
                $items = $item;
                break;
            }
        }
        $spreadsheet->createSheet();//创建sheet
        $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet
        $keys      = $items;//这是你的数据键名
        $count     = count($keys);//计算你所占的列数
        $infoNum   = ceil(count($data['info']) / 2);//求k-v值的所占行数
        $infoStart = $infoNum + 1;//下面的详细信息的开始行数
        $cellName  = array(
            'A',
            'B',
            'C',
            'D',
            'E',
            'F',
            'G',
            'H',
            'I',
            'J',
            'K',
            'L',
            'M',
            'N',
            'O',
            'P',
            'Q',
            'R',
            'S',
            'T',
            'U',
            'V',
            'W',
            'X',
            'Y',
            'Z',
            'AA',
            'AB',
            'AC',
            'AD',
            'AE',
            'AF',
            'AG',
            'AH',
            'AI',
            'AJ',
            'AK',
            'AL',
            'AM',
            'AN',
            'AO',
            'AP',
            'AQ',
            'AR',
            'AS',
            'AT',
            'AU',
            'AV',
            'AW',
            'AX',
            'AY',
            'AZ'
        );
        $sheet     = $spreadsheet->getActiveSheet($n)->setTitle($data['title']);//设置sheet的名称
        foreach ($cellName as $item) {
            $spreadsheet->getActiveSheet($n)->getStyle($item)->getAlignment()
                        ->setHorizontal(Alignment::HORIZONTAL_CENTER)->setWrapText(true); //居中
        }
        $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getAlignment()
                    ->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getFont()->setBold(true); //标题栏加粗
        foreach ($data['rows'] as $key => $item) {
            //循环设置单元格：
            //$key+$infoStart,因为第一行是表头，所以写到表格时   从第数据行开始写
            for ($i = 65; $i < $count + 65; $i++) {
                //数字转字母从65开始：
                $sheet->setCellValue(strtoupper(chr($i)) . ($key + "$infoStart"), $item[$i - 65]);
                $spreadsheet->getActiveSheet($n)->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
            }
        }
        $rowNumber = 0;
        $infoIndex = 0;
        foreach ($data['info'] as $key => $value) {
            if ($infoIndex % 2 == 0) {
                $rowNumber++;
                $infoCellName1      = 'A' . $rowNumber;
                $infoCellMegreRange = 'A' . $rowNumber . ':B' . $rowNumber;
                $infoCellName2      = 'B' . $rowNumber;
            } else {
                $infoCellName1      = 'C' . $rowNumber;
                $infoCellMegreRange = 'C' . $rowNumber . ':D' . $rowNumber;
                $infoCellName2      = 'D' . $rowNumber;
            }
            $spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName1, $key . ':' . $value);
            $spreadsheet->getActiveSheet($n)->mergeCells($infoCellMegreRange);
            $spreadsheet->getActiveSheet($n)->getStyle("$infoCellName1:$infoCellName2")->getfont()->setSize(20)
                        ->setBold(true);
            $infoIndex++;
        }
    }

}
